List records that do not include specified value

[QB]

I have a very basic select query that list all the records from a table

SELECT tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no
FROM tbl_cngu LEFT JOIN tbl_cngu_recalls ON tbl_cngu.cngu_no =
tbl_cngu_recalls.cngu_no
ORDER BY tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no DESC;

Now I need to apply a twist and do no know how.

The [recall_no] field is a numeric field 1,2,3,4,5,... and what I would like
to do is supply the query with a [recall_no] value and have the query return
those records for which there is no such [recall_no] for a each given
[cngu_no]. How would I do this?

Thank you,

QB

 

[John W. Vinson]

I have a very basic select query that list all the records from a table

SELECT tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no
FROM tbl_cngu LEFT JOIN tbl_cngu_recalls ON tbl_cngu.cngu_no =
tbl_cngu_recalls.cngu_no
ORDER BY tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no DESC;

Now I need to apply a twist and do no know how.

The [recall_no] field is a numeric field 1,2,3,4,5,... and what I would like
to do is supply the query with a [recall_no] value and have the query return
those records for which there is no such [recall_no] for a each given
[cngu_no]. How would I do this?

Thank you,

QB

A Subquery with a NOT EXISTS clause should work:

SELECT tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no
FROM tbl_cngu LEFT JOIN tbl_cngu_recalls ON tbl_cngu.cngu_no =
tbl_cngu_recalls.cngu_no
WHERE NOT EXISTS(Select recall_no FROM tbl_cngu AS X
WHERE X.Cngu_no = tbl_cngu.cngu_no AND recall_no = [Enter number to exclude:])
ORDER BY tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no DESC;

This assumes that there may be multiple records for each cngu_no; if not (i.e.
if cngu_no is the Primary Key) it's much easier:

SELECT tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no
FROM tbl_cngu LEFT JOIN tbl_cngu_recalls ON tbl_cngu.cngu_no =
tbl_cngu_recalls.cngu_no
WHERE Recall_No <> [Enter number to exclude:]
ORDER BY tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no DESC;

 

[Rick Brandt]

I have a very basic select query that list all the records from a table

SELECT tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no
FROM tbl_cngu LEFT JOIN tbl_cngu_recalls ON tbl_cngu.cngu_no =
tbl_cngu_recalls.cngu_no
ORDER BY tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no DESC;

Now I need to apply a twist and do no know how.

The [recall_no] field is a numeric field 1,2,3,4,5,... and what I would
like to do is supply the query with a [recall_no] value and have the query
return those records for which there is no such [recall_no] for a each
given
[cngu_no]. How would I do this?

SELECT tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no
FROM tbl_cngu
LEFT JOIN tbl_cngu_recalls
ON tbl_cngu.cngu_no = tbl_cngu_recalls.cngu_no
WHERE tbl_cngu_recalls.recall_no <> [SomeValue]
OR tbl_cngu_recalls.recall_no Is Null
ORDER BY tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no DESC;