kronzzz — 2011-12-20T17:58:12-05:00 — #1
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.
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
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!
eruna — 2011-12-20T19:28:08-05:00 — #2
I'm not sure I understand your post, but it sounds like you are looking for a left join.
r937 — 2011-12-21T02:57:45-05:00 — #3
yup, as eruna said, a left join...
, O.OName AS [Affilated company]
, O2.OName AS [Paying agency]
Participants AS P
JOIN Organisms AS O
ON O.OId = P.OId_affiliates
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
kronzzz — 2011-12-21T05:27:58-05:00 — #4
Woooow! It woooorks!! ))))
Thank you soooo much, eruna and r937!