How to display if a field exists in other table?

Question

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

Answer 1

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