"Distinct" results from SQL query using a mappings table

Question

Hi, I have two tables tbl1 and tbl2, both with two columns, one for id and one for product. I want to extract the rows that are in both, i.e. rows where tbl1.id = tbl2.id and tbl1.product = tbl2.product and join the row from tbl1 and tbl2 into one row.

I imagine this goes something like this:

SELECT tbl1.\*, tbl2.\*
FROM tbl1, tbl2
WHERE tbl1.id = tbl2.id
  AND tbl1.product = tbl2.product

or

SELECT tbl1.\*, tbl2.\*
FROM tbl1
  INNER JOIN tbl2 
    ON tbl1.id = tbl2.id
       AND tbl1.product = tbl2.product

?

I've an added problem, whereby products do not have the same names in the two spreadsheets.

I added a mappings table which holds the product name in tbl1 and it's corresponding tbl2 product name in each row.

How would I know achieve the equivalent of the above SQL query with this added table (where only one row is output for each id/product combination that exists in both)?

Thanks for any help.

(Note: I'm using MS Access)

Answer 1

SELECT 
     tbl1.*, tbl2.*
FROM tbl1, tbl2, tbl3
 WHERE
     tbl1.id = tbl2.id
 AND tbl1.product = tbl3.product1
 AND tbl2.product = tbl3.product2

Answer 2

SELECT
     t1.id,
     t1.product,
     t2.product
FROM
     tbl1 t1
LEFT OUTER JOIN mapping_table map ON
     map.id = tbl1.id
INNER JOIN tbl2 t2 ON
     t2.id = t1.id AND
     (t2.product = t1.product OR t2.product = map.tbl2_product)

You may need to tweak this based on whether or not you want rows to be considered a match if the product names from t1 and t2 are the same, but there's also a match in the map table. Also, if you are planning to have all products in the mapping table even if the names already match between the two.

Answer 3

Just use one of the columns from the mapping table.