SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help about SQL query (displaying empty cells)

    Hi everyone!
    I'm just studing SQL quering in access and I have a problem with one of them. Could anyone help me?

    I need to indicate for each participant his last name, first name, the name of organism he is affiliated to and the name of organism which pays for the participant if there is any.

    My tables:
    PARTICIPANTS (PRegNum, PLAstName, PFirstName, OId_affiliates, OId_is_the_paying_Agency, etc...)
    ORGANISMS (Oid, OName, etc..)

    The problem is that I need to display Oname 2 times in different tables so I wrote this
    SELECT PLastName, PFirstName, O.OName AS [Affilated company], O2.OName AS [Paying agency]
    FROM Participants AS P, Organisms AS O, Organisms AS O2
    WHERE P.OId_affiliates=O.OId
    AND P.OId_is_the_paying_Agency=O2.OId;

    but in this case participants who have no data in OId_is_the_paying_Agency are not displayed but I need them to be dislayed just with an empty cell for this column.

    How should I change my SQL query?
    Thanks a lot!

  2. #2
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    971
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure I understand your post, but it sounds like you are looking for a left join.

    http://www.w3schools.com/sql/sql_join_left.asp

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yup, as eruna said, a left join...
    Code:
    SELECT P.PLastName
         , P.PFirstName
         , O.OName AS [Affilated company]
         , O2.OName AS [Paying agency]
      FROM (
           Participants AS P
    INNER
      JOIN Organisms AS O
        ON O.OId = P.OId_affiliates
           )
    LEFT OUTER
      JOIN Organisms AS O2
        ON O2.OId = P.OId_is_the_paying_Agency
    notice how msaccess requires parentheses for tables being joined if there are more than two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Woooow! It woooorks!! ))))
    Thank you soooo much, eruna and r937!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •