Multiple select queries in WHERE IN statement

Question

I have the following sql query :

SELECT `main_table`.* FROM `prd_brand` AS `main_table`
INNER JOIN 
    (SELECT DISTINCT value from catalog_product_entity_int where row_id in 
        (select row_id from catalog_product_entity_int where attribute_id = 97 and value = 1) , 
        (select row_id from catalog_product_entity_int where attribute_id = 99 and value = 4)) t 
ON main_table.brand_id = t.value

Is that possible to add multiple select queries in the WHERE IN statement.

BTW, when executing the query I have #1248 - Every derived table must have its own alias.

Gordon Linoff · Accepted Answer · 2017-07-03 11:45:11Z

3

I'm not quite sure what your query is trying to do. But this seems like a simpler way to write the logic:

SELECT b.* 
FROM `prd_brand` AS b INNER JOIN 
    (SELECT DISTINCT value 
     FROM catalog_product_entity_int 
     WHERE (attribute_id, value) IN ( (97, 1), (99, 4) )
    ) t
    ON b.brand_id = t.value

answered Jul 3, 2017 at 11:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

androniennn Over a year ago

This query gets all lines that have 97-1 AND 99-4 as attribute_id-value or 97-1 OR 99-4 ? I executed it and it seems like OR (and I need an AND between them).

Gordon Linoff Over a year ago

@androniennn . . . You should ask another question being explicit about what you want. Sample data and desired results help.

Collectives™ on Stack Overflow

Multiple select queries in WHERE IN statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related