Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesDatabase Management

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here
  #1  
Old September 23rd, 2002, 11:52 AM
iamtgo3 iamtgo3 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2001
Location: USA
Posts: 312 iamtgo3 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 27 m 43 sec
Reputation Power: 7
Send a message via ICQ to iamtgo3 Send a message via AIM to iamtgo3 Send a message via MSN to iamtgo3 Send a message via Yahoo to iamtgo3
INNER JOIN query trouble with null join fields

Ok the query I have below works great till I come acrossed a record that does not have a ClientNumber (Meaning it is blank). So in effect what it does is even if the Where clause is satisfied in a record with no ClientNumber it skips it. What I need is a way to include records from the myProject table even if they do not have a ClientNumber for an INNER JOIN. I am thinking this is going to have to be some sort of Nested query or something. My brain hurts from other parts of this project so I am asking it here.

SELECT myProject.*, myClients.*
FROM myProject INNER JOIN myClients
ON myProject.ClientNumber = myClients.ClientNumber
WHERE (myProject.ProjectName LIKE '%IPDG3%')
OR
(myProject.ClientCompanyB LIKE '%IPDG3%')
ORDER BY myProject.ProjectNumber
__________________
George - www.ipdg3.com
Helping Developers and Programmers Find Resources
Forums - Contests - Tutorials - Source Code
ORB - Wireless Site - Online Gear

Reply With Quote
  #2  
Old September 23rd, 2002, 12:04 PM
rod k rod k is offline
Apprentice Deity
Dev Shed Loyal (3000 - 3499 posts)
 
Join Date: Jul 1999
Location: Niagara Falls (On the wrong side of the gorge)
Posts: 3,237 rod k User rank is Private First Class (20 - 50 Reputation Level)rod k User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 4 m 8 sec
Reputation Power: 13
Send a message via AIM to rod k
Use a left join instead of an inner join
__________________
FSBO (For Sale By Owner) Realty

Reply With Quote
  #3  
Old September 23rd, 2002, 01:10 PM
iamtgo3 iamtgo3 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2001
Location: USA
Posts: 312 iamtgo3 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 27 m 43 sec
Reputation Power: 7
Send a message via ICQ to iamtgo3 Send a message via AIM to iamtgo3 Send a message via MSN to iamtgo3 Send a message via Yahoo to iamtgo3
Thanks rod k been so busy on other parts of this program that I was just drawing a blank. New query in case people want to see the fix.

SELECT myProject.*, myClients.*
FROM myProject LEFT OUTER JOIN myClients
ON myProject.ClientNumber = myClients.ClientNumber
WHERE (myProject.ProjectName LIKE '%IPDG3%')
OR
(myProject.ClientCompanyB LIKE '%IPDG3%')
ORDER BY myProject.ProjectNumber

And that is how "rod k" made the "WHOIS the Man" list folks *Grin*

Thanks

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDatabase Management > INNER JOIN query trouble with null join fields


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway