Trix Trix - 1 year ago 60
SQL Question

SQL query to select distinct rows from left table after inner join to the right table

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]

can only have values from

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.title,t1.description,t2.size FROM products t1 INNER JOIN sizes t2 ON 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:


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


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

, what I want as output is:

1 | product1 | description1 | 1,2,4,5
2 | product2 | description2 | 1,5

Answer Source

Your query should be like:

$query = "
    select, t1.title, t1.description, group_concat(t2.size SEPARATOR ",") as sizes
    from products as t1
       inner join sizes as t2 on
    where in (select from sizes as t3 where t3.size in (".$size_list .")
    group by, 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download