join table query

[Guest]

Hi! Please help & thank you in advance.

I have 2 tables (tblT, tblL) with no unique key so, I’ve created autonumber
on both tables.

Query the tblX for below criteria selects 26 records:
SELECT tblX.prodName, tblX.prodID, tblL.prodName, tblL.prodID, tblL.TEST_DATE
FROM tblX
WHERE (((tblX.prodName)="PROD1") AND ((tblX.prodID)="123"))

My objective is to select the “tblL.TEST_DATE†from tblL when prodName &
prodID is equal from both tables but when I use the below SQL it doubles the
records count to 56 records. I think record count should still be 26 but
just show the TEST_DATE from the tblL. Do you see anything wrong with above
SQL statement?

SELECT tblX.prodName, tblX.prodID, tblL.prodName, tblL.prodID, tblL.TEST_DATE
FROM tblX LEFT JOIN tblL ON (tblX.prodName=tblL.prodName) AND
(tblX.prodID=tblL.prodID)
WHERE
(((tblX.prodName)="PROD1") AND (tblX.prodID)="123")) AND
((tblL.prodName)="PROD1") AND (tblL.prodID)="123")));

 

[John Vinson]

Hi! Please help & thank you in advance.

I have 2 tables (tblT, tblL) with no unique key so, I’ve created autonumber
on both tables.

Query the tblX for below criteria selects 26 records:
SELECT tblX.prodName, tblX.prodID, tblL.prodName, tblL.prodID, tblL.TEST_DATE
FROM tblX
WHERE (((tblX.prodName)="PROD1") AND ((tblX.prodID)="123"))

My objective is to select the “tblL.TEST_DATE” from tblL when prodName &
prodID is equal from both tables but when I use the below SQL it doubles the
records count to 56 records. I think record count should still be 26 but
just show the TEST_DATE from the tblL. Do you see anything wrong with above
SQL statement?

Yes; you're selecting TblL.ProdID and tblL.TEST_DATE from tblX. These
fields do not EXIST in TblX.

SELECT tblX.prodName, tblX.prodID, tblL.prodName, tblL.prodID, tblL.TEST_DATE
FROM tblX LEFT JOIN tblL ON (tblX.prodName=tblL.prodName) AND
(tblX.prodID=tblL.prodID)
WHERE
(((tblX.prodName)="PROD1") AND (tblX.prodID)="123")) AND
((tblL.prodName)="PROD1") AND (tblL.prodID)="123")));

I'm confused about your ProdID fields. Is the ProdName unique within
the table (even if it doesn't yet have a unique key)? If not, how can
you be sure that ProdID 123 in tblX has anything whatsoever to do with
ProdID 123 in tblL?

At the very least you need to drop the quotemarks around "123" if
you're using a Number (autonumber) field.

If prodName is unique, you may not need ProdID at all in either table:
try just

SELECT tblX.prodName, tblX.prodID, tblL.prodName, tblL.prodID,
tblL.TEST_DATE
FROM tblX INNER JOIN tblL ON tblX.prodName=tblL.prodName
WHERE tblX.prodName="PROD1";

Since you're already stating that the ProdNames must match in the
JOIN, there's no need to do so in the WHERE.

John W. Vinson[MVP]

 

[Guest]

John, thanks for the reply. After posting the msg I've found it has to do
with data mess not with syntax. Thanks.