SQL查询从内部连接到右侧表格后的左侧表格中选择不同的行 [英] SQL query to select distinct rows from left table after inner join to the right table

问题描述

我有两个MySQL表。这是第一个[产品]（PRIMARY = id）：

  [id]标题|描述
  

这是第二个[sizes]（PRIMARY = id& size）：

  [id] | [size] 
  

size 只能有值从 [1,2,3,4,5,6] 。

我有一个PHP数组，尺寸值。我将其重塑为逗号分隔的值字符串，如下所示：

  $ size_list = implode（，，$ sizes）; 
  

对于不熟悉PHP的人，上述代码将生成一个如下所示的字符串：1 ，4,5，然后查询数据库，如下所示：

  $ query =SELECT t1.id，t1.title， t1.description，t2.size FROM products t1 INNER JOIN sizes t2 ON t1.id = t2.id WHERE size IN（。$ size_list。）; 
  

但是，此查询会根据尺寸表中的每个尺寸重复产品。
我想要：

从产品表中返回在尺寸表中至少有一个可用尺寸的记录，而不重复

当然

希望变量中的这些大小向客户端显示 / p>

---

例如：

产品：

  1 | product1 | description1 
 2 | product2 | description2 
 3 | product3 | description3 
 4 | product4 | description4 
  

尺寸：

  1 | 1 
 1 | 2 
 1 | 4 
 1 | 5 
 2 | 1 
 2 | 5 
  

给定 $ sizes_list =1,2，我想要的输出是：

  1 | product1 | description1 | 1,2,4,5 
 2 | product2 | description2 |你的查询应该是这样的：
 p> 
 
 
  $ query =
 select t1.id，t1.title，t1.description，group_concat（t2.size SEPARATOR ，）作为尺寸
从产品作为t1 
内部连接大小作为t2在t1.id = t2.id 
其中t1.id（（选择t3.id从大小为t3，其中t3 .size in（。$ size_list）
 group by t1.id，t1.title，t1.description 

  
有一点解释当你加入两个表时，你可以从表大小中获取所有 id 从表产品，所以id = 1加入了四个记录，id = 2加上两个记录，所以你必须聚合这个数字成为一个记录。
 
I have two MySQL tables. This is first one [products] (PRIMARY = id):
[id] | title | description
and this is the second [sizes] (PRIMARY = id & size):
[id] | [size]
size can only have values from [1,2,3,4,5,6].

I have a PHP array which has the size values. I reshape it to a comma-separated values string like this:
$size_list = implode(",", $sizes);
For those who are not familiar with PHP, the above code will generate an string like this: "1,4,5" and then query the database like this:
$query = "SELECT t1.id,t1.title,t1.description,t2.size FROM products t1 INNER JOIN sizes t2 ON t1.id=t2.id WHERE size IN(".$size_list .")";
But this query replicates the products for each size they have in the sizes table.
I want to:

Return records from products table which have at least one available size in sizes table, without duplicate

and of course

want those sizes in a variable to show to the client



For example:

Products:
1 | product1 | description1
2 | product2 | description2
3 | product3 | description3
4 | product4 | description4
Sizes:
1 | 1
1 | 2
1 | 4
1 | 5
2 | 1
2 | 5
Given $sizes_list="1,2", what I want as output is:
1 | product1 | description1 | 1,2,4,5
2 | product2 | description2 | 1,5

解决方案
Your query should be like:
$query = "
    select t1.id, t1.title, t1.description, group_concat(t2.size SEPARATOR ",") as sizes
    from products as t1
       inner join sizes as t2 on t1.id=t2.id
    where t1.id in (select t3.id from sizes as t3 where t3.size in (".$size_list .")
    group by t1.id, t1.title, t1.description
"
A bit of explanation. When you join two tables, you get all rows from table sizes for all id from table products, so id = 1 joined with four records and id = 2 joined with two records. So you have to aggregate this numbers into one record.

                        
这篇关于SQL查询从内部连接到右侧表格后的左侧表格中选择不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！