Combining 2 MySQL queries

Question

I have 2 queries:

SELECT     sql_cache distinct p.products_image, 
           p.products_subimage1, 
           pd.products_name, 
           p.products_quantity, 
           p.products_model, 
           p.products_ordered, 
           p.products_id, 
           p.products_price, 
           p.products_date_added, 
           p.products_weight, 
           p.products_length, 
           p.products_width, 
           p.products_height, 
           p.products_tax_class_id, 
           p.products_status, 
           IF(s.status, s.specials_new_products_price, NULL)             AS specials_new_products_price,
           IF(s.status, s.specials_new_products_price, p.products_price) AS final_price 
FROM       products p 
LEFT JOIN  specials s 
ON         p.products_id = s.products_id 
LEFT JOIN  products_to_categories p2c 
ON         p.products_id=p2c.products_id 
LEFT JOIN  products_description pd 
ON         p.products_id=pd.products_id 
INNER JOIN filter_association_products fap 
ON         p.products_id =fap.products_id 
LEFT JOIN  products_attributes pa 
ON         p.products_id = pa.products_id 
WHERE      p.products_status = '1' 
AND        date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added 
AND        fap.filter_id = 126 
ORDER BY   p.products_date_added DESC, 
           pd.products_name

Which gives me a result of 52 rows (products).

And an identical query with only difference:

AND fap.filter_id = 130

Which gives me a result of 4 rows.

One of the common things between these rows/products is that 3 out of the 4 with filter_id 130 also have filter_id 126 and I want to modify the query to give me results of only products with both (or even more, depends on the filter_ids applied) of the indicated filter_id.

I have tried

... 
AND FIND_IN_SET(fap.filter_id,'126', '130') 
ORDER BY p.products_date_added DESC, pd.products_name

But I get a result of 53 rows/products, meaning it's showing all the products that have either filter, while the result I am looking for in this case is of only the 3 rows that have both filter_id.

What is the best way to rewrite the query to get the correct results?

Possible duplicate of [SQL for applying conditions to multiple rows in a join](http://stackoverflow.com/questions/4763143/sql-for-applying-conditions-to-multiple-rows-in-a-join) — Clockwork-Muse, Dec 27 '15 at 13:01

Gordon Linoff · Answer 1 · 2015-12-27T22:09:42.407

2

Use GROUP BY to get the product information that you want. You can join in additional information as well, but this is the basic query:

SELECT p.*
FROM products p JOIN
     specials s 
     ON p.products_id = s.products_id JOIN
     products_to_categories p2c 
     ON p.products_id = p2c.products_id JOIN
     products_description pd 
     ON p.products_id = pd.products_id JOIN
     filter_association_products fap 
     ON p.products_id = fap.products_id JOIN
     products_attributes pa 
     ON p.products_id = pa.products_id 
WHERE p.products_status = '1' AND
      date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added AND
      fap.filter_id IN (126, 130)
GROUP BY p.products_id
HAVING COUNT(DISTINCT fap.filter_id) = 2;  -- make sure both match

I don't see any reason for the LEFT JOINs (at least for most of the tables, because everything up to filter_association_products is being turned into an inner join by the WHERE clause), so I changed them to inner joins.

edited Dec 27 '15 at 22:09

answered Dec 27 '15 at 13:34

Gordon Linoff

1,242,037
58
646
786

Thanks, Gordon. Executing this query as is gives sql error. Changing `HAVING COUNT(DISTINCT fap.filter_id) = 2;` or `HAVING COUNT(DISTINCT fap.filter_id = 2);` give no results at all (zero results) – Nikita 웃 Dec 27 '15 at 13:50
chage `HAVING COUNT(DISTINCT fap.filter_id = 2; -- make sure both match` to `HAVING COUNT(DISTINCT fap.filter_id) = 2; -- make sure both match` (can't edit just one sign :( ) – Mikey Dec 27 '15 at 14:25
@Mikey I tried that as I wrote, but get no results :( – Nikita 웃 Dec 27 '15 at 14:28
Bring Left joins back and you'll get your result. – Mikey Dec 27 '15 at 14:32
@CreativeMind . . . The closing paren goes before the `=`. – Gordon Linoff Dec 27 '15 at 22:10

Mikey · Answer 2 · 2015-12-27T14:12:16.127

Make two LEFT JOIN's on filter_association_products and filter in WHERE, like this:

 SELECT     sql_cache distinct p.products_image, 
               p.products_subimage1, 
               pd.products_name, 
               p.products_quantity, 
               p.products_model, 
               p.products_ordered, 
               p.products_id, 
               p.products_price, 
               p.products_date_added, 
               p.products_weight, 
               p.products_length, 
               p.products_width, 
               p.products_height, 
               p.products_tax_class_id, 
               p.products_status, 
               IF(s.status, s.specials_new_products_price, NULL)             AS specials_new_products_price,
               IF(s.status, s.specials_new_products_price, p.products_price) AS final_price 
    FROM       products p 
    LEFT JOIN  specials s 
    ON         p.products_id = s.products_id 
    LEFT JOIN  products_to_categories p2c 
    ON         p.products_id=p2c.products_id 
    LEFT JOIN  products_description pd 
    ON         p.products_id=pd.products_id 
    LEFT JOIN filter_association_products fap1 
    ON         p.products_id =fap1.products_id 
    LEFT JOIN filter_association_products fap2 
    ON         p.products_id =fap2.products_id 
    LEFT JOIN  products_attributes pa 
    ON         p.products_id = pa.products_id 
    WHERE      p.products_status = '1' 
    AND        date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added 
    AND        fap1.filter_id = 126 
    AND        fap2.filter_id = 130 
    ORDER BY   p.products_date_added DESC, 
               pd.products_name

Another approach (sub select) :

 SELECT     sql_cache distinct p.products_image, 
               p.products_subimage1, 
               pd.products_name, 
               p.products_quantity, 
               p.products_model, 
               p.products_ordered, 
               p.products_id, 
               p.products_price, 
               p.products_date_added, 
               p.products_weight, 
               p.products_length, 
               p.products_width, 
               p.products_height, 
               p.products_tax_class_id, 
               p.products_status, 
               IF(s.status, s.specials_new_products_price, NULL)             AS specials_new_products_price,
               IF(s.status, s.specials_new_products_price, p.products_price) AS final_price 
    FROM       products p 
    LEFT JOIN  specials s 
    ON         p.products_id = s.products_id 
    LEFT JOIN  products_to_categories p2c 
    ON         p.products_id=p2c.products_id 
    LEFT JOIN  products_description pd 
    ON         p.products_id=pd.products_id 
    LEFT JOIN  products_attributes pa 
    ON         p.products_id = pa.products_id 
    WHERE      p.products_status = '1' 
    AND        date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added 
    AND  126 in (select fap1.filter_id from filter_association_products fap1 
                 where p.products_id = fap1.products_id)
    AND  130 in (select fap2.filter_id  from  filter_association_products fap2 
                 where p.products_id = fap2.products_id)
    ORDER BY   p.products_date_added DESC, 
               pd.products_name

... `LEFT JOIN` with a condition in the `WHERE` is the equivalent of an `INNER JOIN`. This is also not extensible in the long term, given the number of extra joins required. — Clockwork-Muse, Dec 27 '15 at 12:55
That's true, but is the simplest plain SQL, that will work for the given request. Also inner join vs left plus where is a taste thing. — Mikey, Dec 27 '15 at 13:01
No, it's not; it's an error and a danger for future maintainers. It may also impose a performance penalty (on dumb RBMSs). Be clear in your code. Do you _for sure_ want that relation, and exclude it if it doesn't exist? Than use an `INNER JOIN` (and preferably stick all relevant relations in the `ON` clause). Do you want that relation if the data exists, but only the 'main' data if it doesn't? Use `LEFT JOIN`. I would require changes if this came up in a code review. — Clockwork-Muse, Dec 27 '15 at 13:10
thanks for the edit, Mikey, now it works too. Reading the comments, I am a bit concerned about the performance impact of this approach as we do have many filters to apply to queries, not only 2 as in the example. @Clockwork-Muse - is Gordon's answer what you meant? — Nikita 웃, Dec 27 '15 at 14:20
I fixed the second query. As for the comments: in this case there is no difference in the result between LEFT and INNER JOIN and Clockwork-Muse just says INNER JOIN is clearer. Another point is: if you use one of the queries, you have in first statement to add one join and one where clause and in second statement where clause with subselect for each further filter id, so it is not good extesible. — Mikey, Dec 27 '15 at 14:22
Thanks. so which approach out of the 3 on this page is best for extensibility and performance, considering there are many `filter_id`s? — Nikita 웃, Dec 27 '15 at 14:25
I would not care for the performance to early - if you have a problems with performance test all 3: take @Gordon Linoff answer for extesibility ;) — Mikey, Dec 27 '15 at 14:28

Nikita 웃 · Accepted Answer · 2015-12-27T20:25:58.443

While both answers by Mikey and Gordon Linoff are good, I preferred Gordon's approach due to its extensibility. However, his answer has some syntax errors and the switch to JOINs didn't produce any results, so I had to revise them back to LEFT JOINs, as suggested by Mikey. (Thank you both). This is the final query that works:

SELECT     sql_cache distinct p.products_image, 
           p.products_subimage1, 
           pd.products_name, 
           p.products_quantity, 
           p.products_model, 
           p.products_ordered, 
           p.products_id, 
           p.products_price, 
           p.products_date_added, 
           p.products_weight, 
           p.products_length, 
           p.products_width, 
           p.products_height, 
           p.products_tax_class_id, 
           p.products_status, 
           IF(s.status, s.specials_new_products_price, NULL)             AS specials_new_products_price,
           IF(s.status, s.specials_new_products_price, p.products_price) AS final_price 
FROM       products p 
LEFT JOIN  specials s 
ON         p.products_id = s.products_id 
LEFT JOIN  products_to_categories p2c 
ON         p.products_id=p2c.products_id 
LEFT JOIN  products_description pd 
ON         p.products_id=pd.products_id 
INNER JOIN filter_association_products fap 
ON         p.products_id =fap.products_id 
LEFT JOIN  products_attributes pa 
ON         p.products_id = pa.products_id 

WHERE p.products_status = '1' 
AND    date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added 
AND      fap.filter_id IN (126, 130)
GROUP BY p.products_id
HAVING COUNT(DISTINCT fap.filter_id) = 2;

... `DISTINCT` should be unnecessary here; the `GROUP BY` will ensure you're only getting one product_id, which would make data from that table unique as well. If you're getting multiple values from one of the other tables (such as the name), it won't help you in the case they're different (you'd get rows with two different names); you'd need to pick a specific row (usually "most recent"). You don't use columns from either `products_to_categories` or `products_attributes`, so you can remove them from the query — Clockwork-Muse, Dec 27 '15 at 23:11

Combining 2 MySQL queries

3 Answers3