Another request for query help

[vb6novice]

Is there a way to get all of the records in table X that do not have a corresponding record in table Y?

Here's some details:
Table X fields:
POKEY
EXTERNKEY
MODEL
QTY

Table Y fields:
POKEY
EXTERNKEY
MODEL
QTY

Table X contains all receipts for 1 week and can have many records (typically 8-10) for a given POKEY and EXTERNKEY combination (different MODELs). This table has about 64000 records.

Table Y contains all the POs for 3 weeks and can also have many records (also typically 8-10) for a given POKEY and EXTERNKEY (different MODELs). This table has about 217000 records.

Most Table X records have a Table Y record where POKEY, EXTERNKEY, and MODEL all match. Sometimes, Table X has a record where only POKEY and EXTERNKEY match, but the Table X MODEL does not match the Table Y MODEL (this could happen when an item was received which was not on the PO, or the item was received in as the wrong model, or the PO listed the wrong model).

How do I structure a query that finds only the records in Table X that have a matching POKEY and EXTERNKEY in Table Y, but the MODELS do not match?


The following attempts have failed:
X LEFT JOIN Y ON X.POKEY = Y.POKEY and X.EXTERNKEY = Y.EXTERNKEY and X.MODEL = Y.MODEL
WHERE X.MODEL <> Y.MODEL
(I got zero records - yeah, I see why)

X LEFT JOIN Y ON X.POKEY = Y.POKEY and X.EXTERNKEY = Y.EXTERNKEY
WHERE X.MODEL <> Y.MODEL
(I got every record in Table X)

X INNER JOIN Y ON X.POKEY = Y.POKEY and X.EXTERNKEY = Y.EXTERNKEY
WHERE X.MODEL <> Y.MODEL
(I get the number of Table X records for the POKEY and EXTERNEKY combination (8-10) times the number of Table Y records for the POKEY and EXTERNKEY combination (8-10) minus one in each repeated group for the matching record (total about 720000 records).

How do I get the Table X records that match 2 but not 3 fields?

Thanks for ant and all assistance.

 

[PHV]

What about this ?
X LEFT JOIN Y ON X.POKEY = Y.POKEY and X.EXTERNKEY = Y.EXTERNKEY and X.MODEL = Y.MODEL
WHERE Y.MODEL Is Null

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

 

[randy700]

Or, simply use the built in Unmatched Query Wizard.


Randy

 

[HelsByTheDock]

IMHO, EXISTS is a great keyword for these type of queries.

eg.

SELECT * FROM table1
WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field1)

Basically, this selects everything from the first table where it doesn't match the criteria in the subquery.