3 table select query

[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

 

[James]

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;

 

[MikeR]

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!