SQL help

[MikeR]

For my Jet database I have a query that almost works, but I can't get the last piece
right.

SELECT C_List.CID, C_List.LName FROM (C_List LEFT JOIN Alias ON C_List.CID =
Alias.CID) LEFT JOIN AWARD ON C_List.CID = AWARD.ACID WHERE (((AWARD.ACID) Is Null)
AND ((Alias.Date_To)Is Null)) GROUP BY C_List.CID, C_List.LName ORDER BY C_List.CID;

The Alias table has a yes/no field named 'deleted'. I need to exclude records from
the return set where that field is true.

Thanks, Mike

 

[Dirk Goldgar]

For my Jet database I have a query that almost works, but I can't get the
last piece right.

SELECT C_List.CID, C_List.LName FROM (C_List LEFT JOIN Alias ON C_List.CID
= Alias.CID) LEFT JOIN AWARD ON C_List.CID = AWARD.ACID WHERE
(((AWARD.ACID) Is Null) AND ((Alias.Date_To)Is Null)) GROUP BY C_List.CID,
C_List.LName ORDER BY C_List.CID;

The Alias table has a yes/no field named 'deleted'. I need to exclude
records from the return set where that field is true.

How about:

SELECT
C_List.CID, C_List.LName
FROM
(C_List LEFT JOIN Alias ON C_List.CID = Alias.CID)
LEFT JOIN AWARD ON C_List.CID = AWARD.ACID
WHERE AWARD.ACID Is Null
AND Alias.Date_To Is Null
AND (Alias.Deleted = 0 OR Alias.Deleted Is Null)
GROUP BY C_List.CID, C_List.LName
ORDER BY C_List.CID;

 

[MikeR]

How about:

SELECT
C_List.CID, C_List.LName
FROM
(C_List LEFT JOIN Alias ON C_List.CID = Alias.CID)
LEFT JOIN AWARD ON C_List.CID = AWARD.ACID
WHERE AWARD.ACID Is Null
AND Alias.Date_To Is Null
AND (Alias.Deleted = 0 OR Alias.Deleted Is Null)
GROUP BY C_List.CID, C_List.LName
ORDER BY C_List.CID;

Perfect!
Thanks, Dirk

 

[MikeR]

How about:

SELECT
C_List.CID, C_List.LName
FROM
(C_List LEFT JOIN Alias ON C_List.CID = Alias.CID)
LEFT JOIN AWARD ON C_List.CID = AWARD.ACID
WHERE AWARD.ACID Is Null
AND Alias.Date_To Is Null
AND (Alias.Deleted = 0 OR Alias.Deleted Is Null)
GROUP BY C_List.CID, C_List.LName
ORDER BY C_List.CID;

PERFECT!
Thanks Dirk

Not sure where my reply went, so if the is a dupe, I'm sorry.