Select data from two tables 1

[tkleader]

Tab1 Data:
UNAME AGR_NAME TCODE
TTRAN F:AR_CLERK F.64
TTRAN F:AR_CLERK F-07
TTRAN F:AR_CLERK F-32
TTRAN F:AR_CLERK F-43
TTRAN F:AR_CLERK FB01
TTRAN F:AR_CLERK FB12
TTRAN F:AR_CLERK FB15
TTRAN F:AR_CLERK FB17
TTRAN F:AR_CLERK FBL5N
TTRAN F:AR_CLERK FBP1

Tab2 Data
TCODE1 TCODE2
F-43 FBP1
F-43 F-07

Query:
SELECT [TAB1].[UNAME], [TAB1].[AGR_NAME], [TAB2].[TCODE], [TAB2].[SOD_TCODE]
FROM TAB1, TAB2
WHERE (((TAB2.TCODE) In (select tcode from TAB1)) AND ((TAB2.SOD_TCODE) In (select tcode from TAB1)));

Incorrect Result:

TTSAN F:AR_CLERK F-43 FBP1 (Repeated 50 rows)
TTSAN F:AR_CLERK F-43 F-07 (Repeated 50 rows)

When I added "DISTINCT" in the select statement, the query takes forever to run. The correct result only appear when I hit CTRL + Break.

Desire Result:
TTSAN F:AR_CLERK F-43 FBP1
TTSAN F:AR_CLERK F-43 F-07

Can someone help me please?

 

[SBendBuckeye]

Try this instead:

SELECT TAB1.UNAME,TAB1.AGR_NAME,TAB2.TCODE,TAB2.SOD_TCODE
FROM TAB1 INNER JOIN TAB2
ON TAB1.TCODE = TAB2.TCODE AND TAB1.TCODE = TAB2.SOD_TCODE;

Good Luck!

Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[SBendBuckeye]

The query above should probably be an OR instead of AND.

SELECT TAB1.UNAME,TAB1.AGR_NAME,TAB2.TCODE,TAB2.SOD_TCODE
FROM TAB1 INNER JOIN TAB2
ON TAB1.TCODE = TAB2.TCODE OR TAB1.TCODE = TAB2.SOD_TCODE;

Good Luck!
Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!