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.
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.