Need help about SQL query (displaying empty cells)

Databases
1

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

I’m not sure I understand your post, but it sounds like you are looking for a left join.

3

yup, as eruna said, a left join…


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

4

Woooow! It woooorks!! ))))
Thank you soooo much, eruna and r937!