CODEWITHSUNDEEP
sql-serversql-server-2000
pvzkch
pvzkch

Reputation: 341

How to display if a field exists in other table?

I want to show/display if the product is found each transaction.

tblProducts

ID     PRODCODE    PRODDESC
1      PFX-321     MILK CHOCO
2      PDF-875     COFFEE JELLY
3      PWA-718     MILK SHAKE

tblTransactions

TCODE       PRODCODE
BMX2213391  PFX-321
BMX2213391  PDF-875
PDFSD92851  PDF-875

I want the results to display like this

TCODE       PRODCODE    FOUND
BMX2213391  PFX-321     YES
BMX2213391  PDF-875     YES
BMX2213391  PWA-718     NO
PDFSD92851  PFX-321     NO
PDFSD92851  PDF-875     YES
PDFSD92851  PWA-718     NO

I tried, INNER JOIN, FULL OUTER JOIN, LEFT JOIN and RIGHT JOIN but I don't get the exact data I need.

Here are the queries I test.

SELECT * FROM tblProducts a INNER JOIN tblTransactions b ON a.PRODCODE = b.PRODCODE
SELECT * FROM tblProducts a FULL OUTER JOIN tblTransactions b ON a.PRODCODE = b.PRODCODE
SELECT * FROM tblProducts a LEFT JOIN tblTransactions b ON a.PRODCODE = b.PRODCODE
SELECT * FROM tblProducts a RIGHT JOIN tblTransactions b ON a.PRODCODE = b.PRODCODE

Upvotes: 0

Views: 45

Answers (2)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36591

http://sqlfiddle.com/#!3/65eb1/24

select DT.TCODE, DT.PRODCODE, case when (Tr2.TCODE IS null and Tr2.PRODCODE IS null) then 'No' else 'Yes' END as FOUND
 from tblTransactions Tr2 right join 
(
select distinct Tr.TCODE, p.PRODCODE               
from   tblProducts p  cross join tblTransactions Tr
) DT
on DT.PRODCODE = Tr2.PRODCODE and DT.TCODE = Tr2.TCODE;

Upvotes: 1

Greg the Incredulous
Greg the Incredulous

Reputation: 1836

I'm pretty sure this works - SQLFiddle here: http://sqlfiddle.com/#!3/65eb1/23

WITH AllVals AS 
(SELECT a.PRODCODE, b.TCODE
FROM tblProducts a
CROSS JOIN tblTransactions b)

SELECT DISTINCT c.PRODCODE,
  c.TCODE,
  CASE WHEN d.PRODCODE IS NULL THEN 'NO' ELSE 'YES' END AS FOUND
FROM AllVals c
LEFT OUTER JOIN tblTransactions d
ON c.PRODCODE = d.PRODCODE
AND c.TCODE = d.TCODE

Upvotes: 1

Related Questions