#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Location
    USA
    Posts
    313
    Rep Power
    18

    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
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Use a left join instead of an inner join
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Location
    USA
    Posts
    313
    Rep Power
    18
    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

IMN logo majestic logo threadwatch logo seochat tools logo