Problem with query

[Bartzilla]

Hi everyone,

I am working on a website in .asp in combination with an Access 2000 database.
I am letting the database do al the querying and just stumbled upon a problem I hadn't even noticed before.
For this query, I have 3 tables:

-Received: stores info about cd's
-Bands: stores bandnames
-Journalists: stores names of journalists

SQL:

SELECT Received.Id, Bands.Bandname, Received.CdTitle, Journalists.FirstName, Journalists.Journalist_ID
FROM (Bands INNER JOIN (Received INNER JOIN Label ON Received.LabelID = Label.LabelID) ON Bands.Band_ID = Received.Bandname) INNER JOIN Journalists ON Recieved.Journalist_ID = Journalists.Journalist_ID;

This query should tell me which cd was given to which journalists. And it does, except for one problem: when in the table 'Received' the field Journalist_ID is empty (because the cd hasn't been handed out yet), the field simply does not show up in the query.
Any idea what might cause this?
Thanks a lot!

 

[PHV]

You may try to replace this:
INNER JOIN Journalists
By this:
LEFT JOIN Journalists

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bartzilla]

I replaced the INNER JOIN with LEFT JOIN, and it seems to do the trick.
Can you explain what exactly is the difference between both joins?
Thanks for helping me out.

 

[PHV]

FROM tableA LEFT JOIN tableB ...
Take all occurrences of tableA and only matching occurrences of tableB. For records of tableA without matching tableB all the columns of tableB are returned as Null.
This is called a LEFT OUTER JOIN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Here's a great article on SQL Joins

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell