组合3个不同的MySQL表的值，连接表1和表2，并检查此连接中是否存在表3值

问题描述：

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?

我有3个MySQL表来检索它们的数据。 p>

table1包含行： p>

  id | 名字|  total_amount |  product_id | 日期
 --------------------------------------------- \ N1  | 一些名字| 一些数量| 一些pid | 一些日期
2 | 一些名字1 | 一些金额1 | 一些pid1 | 一些date1 
3 | 一些名字2 | 一些金额2 | 一些pid2 | 一些date2 
4 | 一些名字3 | 一些金额3 | 一些pid3 |  some date 
  code>  pre> 
 
 和table2： p> 
 
 
  product_id |  product_name 
 ------------------- 
some pid | 一些产品名称
some pid1 | 一些产品名称1 
some pid2 | 一些产品名称2 
some pid3 | 某些产品名称3 
  code>  pre> 
 
 和table3： p> 
 
 
  id |  total_amount |  product_id 
 ------------------------------- 
1 | 一些数量| 一些pid 
2 | 一些金额2 | 一些pid2 
  code>  pre> 
 
 我在table1和table2之间使用内部联接，其product_id列出如下值： p> 
 
 
 名称|  total_amount |  product_id |  product_name | 日期
 -----------------------------------------------  --- 
some名称| 一些数量| 一些pid | 一些产品名称| 一些日期
some name1 | 一些金额1 | 一些pid1 | 一些产品名称1 | 一些date1 
some name2 | 一些金额2 | 一些pid2 | 一些产品名称2 | 一些date2 
some name3 | 一些金额3 | 一些pid3 | 一些产品名称3 | 某些date3 
  code>  pre> 
 
 但是，如果table3的total_amount和product_id与table1的total_amount和product_id具有相同的值，我不想在连接表列表中显示行。 p  > 
 
 
所以我希望我的输出如下： p> 
 
 
  name |  total_amount |  product_id |  product_name | 日期
 -----------------------------------------------  --- 
some name1 | 一些金额1 | 一些pid1 | 一些产品名称1 | 一些date1 
some name3 | 一些金额3 | 一些pid3 | 一些产品名称3 |  some date 
  code>  pre> 
 
 是否可以使用SQL Query执行此操作，还是应该尝试使用客户端语言PHP？ p> 
   DIV>

答

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.

答

The straight-forward way is to use NOT EXISTS or NOT IN :

select t1.name, t1.total_amount, t1.product_id, t2.product_name, t1.date
from t1
join t2 on t2.product_id = t1.product_id
where (t1.total_amount, t1.product_id) not in
(
  select t3.total_amount, t3.product_id
  from t3
);

组合3个不同的MySQL表的值，连接表1和表2，并检查此连接中是否存在表3值

相关推荐