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!