I have 2 tables that contains product sets IDs containing product IDs. Nid is product set ID and second row contains products attached to the set. The reason why 2 tables is that first table contains paid products, second table contains free products. Based on existing products in cart, I created a query to get results like this:

1 product in cart having ID = 45 result:

query: ($items_string is variable containing product IDs)

SELECT i.nid, GROUP_CONCAT(i.field_prod_in_set_nid SEPARATOR ',') as set_products, ad.additional 
FROM {content_field_prod_in_set} i      
JOIN (
    SELECT c.nid, GROUP_CONCAT(c.field_additional_set_prod_nid SEPARATOR ',') as additional
    FROM
    {content_field_additional_set_prod} c
    GROUP BY c.nid) ad ON ad.nid = i.nid    
WHERE 
    i.nid IN (SELECT nid FROM {content_field_prod_in_set} WHERE field_prod_in_set_nid IN ($items_string))   
GROUP BY i.nid

content_field_prod_in_set

content_field_additional_set_prod

QUESTION : Is it possible to write the query without having select in WHERE ? I read that this is not a good practice and it is slow. Thank you.

CodePudding user response：

Join with the subquery instead of using WHERE IN.

SELECT i.nid, GROUP_CONCAT(i.field_prod_in_set_nid SEPARATOR ',') as set_products, ad.additional 
FROM content_field_prod_in_set i      
JOIN (
    SELECT c.nid, GROUP_CONCAT(c.field_additional_set_prod_nid SEPARATOR ',') as additional
    FROM
    content_field_additional_set_prod c
    GROUP BY c.nid) ad ON ad.nid = i.nid    
JOIN (SELECT DISTINCT nid
      FROM content_field_prod_in_set
      WHERE field_prod_in_set_nid IN ($items_string)
     ) i2 ON i2.nid = i.nid
GROUP BY i.nid

DEMO

SELECT DISTINCT is needed in the subquery to avoid duplicating the results in the GROUP_CONCAT() for each matching nid.