3 table select query

M

MikeR

I have 3 tables in an Access 2000 DB.
C_List
CID
Name
Award
CID
Alias
CID
Date_To
I need to return all CID and Name from C_List where CID is NOT in Award and
Date_To for that CID in Alias is NULL.

I moved Date_To from C_List to Alias. When it was 2 tables, this was my query. I
just can't get my head around 3.

SQL := "SELECT [C_List].[CID], [C_List].[LName] FROM C_List LEFT" +
" JOIN Award ON [C_List].[CID] = [Award].[ACID] WHERE " +
"([Award].[ACID] Is Null" +
" and [C_List].[Date_to] is NULL) ORDER BY [C_List].[CID];"
Mike
 
G

Guest

Give this a try...

SELECT C_List.CID, C_List.Name
FROM (C_List LEFT JOIN Alias ON C_List.CID = Alias.CID) LEFT JOIN Award ON
C_List.CID = Award.CID
WHERE (((Award.CID) Is Null) AND ((Alias.Date_To) Is Null))
GROUP BY C_List.CID, C_List.Name
ORDER BY C_List.CID;
 
M

MikeR

James said:
Give this a try...

SELECT C_List.CID, C_List.Name
FROM (C_List LEFT JOIN Alias ON C_List.CID = Alias.CID) LEFT JOIN Award ON
C_List.CID = Award.CID
WHERE (((Award.CID) Is Null) AND ((Alias.Date_To) Is Null))
GROUP BY C_List.CID, C_List.Name
ORDER BY C_List.CID;
Thanks James -
Works like a charm!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL help 3
Jet error 6
empty drop down box 4
Select query help 4
Only return top 5 records per category 1
Select query help 11
Quering Table 5
VBA coding Questions 2

Top