kazata kazata - 6 months ago 9
SQL Question

Combining values of 3 different MySQL table, join table 1 and table 2 and check if table 3 values exist in this join

I have 3 MySQL tables to retrieve data of them.

table1 contains the rows:

id | name | total_amount | product_id | date
---------------------------------------------
1 | some name | some amount | some pid | some date
2 | some name1 | some amount1 | some pid1 | some date1
3 | some name2 | some amount2 | some pid2 | some date2
4 | some name3 | some amount3 | some pid3 | some date3


and table2:

product_id | product_name
-------------------
some pid | some product name
some pid1 | some product name1
some pid2 | some product name2
some pid3 | some product name3


and table3:

id | total_amount | product_id
-------------------------------
1 | some amount | some pid
2 | some amount2 | some pid2


I am using inner join between table1 and table 2 with their product_id to list values as following:

name | total_amount | product_id | product_name | date
--------------------------------------------------
some name | some amount | some pid | some product name | some date
some name1 | some amount1 | some pid1 | some product name1 | some date1
some name2 | some amount2 | some pid2 | some product name2 | some date2
some name3 | some amount3 | some pid3 | some product name3 | some date3


But, I dont want to show rows in join table list if total_amount and product_id of table3 has same values with total_amount and product_id of table1.

So I want my output be like:

name | total_amount | product_id | product_name | date
--------------------------------------------------
some name1 | some amount1 | some pid1 | some product name1 | some date1
some name3 | some amount3 | some pid3 | some product name3 | some date3


Is it possible to do this with SQL Query or should I try to do it with my client side language PHP?

Answer

Yu can use the following query:

SELECT t1.*, t2.*
FROM table1 AS t1
JOIN table2 AS t2 ON t1.product_id = t2.product_id 
LEFT JOIN table3 AS t3 ON t3.product_id = t2.product_id AND t1.total_amount = t3.total_amount
WHERE t3.product_id IS NULL

The above query joins table1 and table2 on field product_id and filters out records in case there is a matching record in table3 having the same product_id and total_amount values.