MySQL select where ID is IN (SELECT another table)

Question

the problem is that I need to select values from table

category_filter_relations

id   modification_id

where my entered modification_id is equal to a variable and also I need to join another table

category_filter_relations_items

id   modification_id   category_filter_relation_id

where can be multiple entries with the same modification_id and different category_filter_relation_id and I need to select those category_filter_relations items.

My query:

SELECT cfr.id AS category_filter_relation_id, 
       cfr.modification_id,
FROM category_filter_relations cfr
WHERE 1
AND cfr.modification_id = '18340300'
AND category_filter_relation_id 
IN (SELECT category_filter_relations_items.category_filter_relation_id 
    FROM category_filter_relations_items 
    WHERE category_filter_relations_items.modification_id = '18340300')

And this way I get an error:

Unknown column 'category_filter_relation_id' in 'IN/ALL/ANY subquery'

Also I tried to join the

LEFT JOIN category_filter_relations_items ON (category_filter_relations_items.category_filter_relation_id = category_filter_relation_id)

but this way I get all the entries from category_filter_relations_items table and I need the entries from

category_filter_relations where category_filter_relations.modification_id = 18340300

joined with

category_filter_relations_items.category_filter_relation_id where category_filter_relations_items.modification_id = 18340300

Data example of what I need:

category_filter_relations
id    modification_id
23796 18340300

category_filter_relations_items 
id      category_filter_relation_id  modification_id     
54690   23270                        18340300

I am selecting the table category_filter_relations and the result should be:

id    modification_id
23796 18340300
23270 11111111

It took the id 23270 because category_filter_relations_items.modification_id was equal to 18340300

Answer 1

This is your tables :

category_filter_relations
=========================
id |  modification_id

and

category_filter_relations_items
===================================================
id | modification_id | category_filter_relation_id

In your query, you do :

SELECT cfr.id AS category_filter_relation_id, -- You select the category_filter_relations id
       cfr.modification_id,                   -- You select the modification_id
FROM category_filter_relations cfr            -- Both from the table category_filter_relations
WHERE 1
AND cfr.modification_id = '18340300'          -- You add a condition on the modification_id
AND category_filter_relation_id               -- You add a condition on an id not in the table category_filter_relations
IN (SELECT category_filter_relations_items.category_filter_relation_id 
    FROM category_filter_relations_items 
    WHERE category_filter_relations_items.modification_id = '18340300')

The output you want is :

id    modification_id
23796 18340300
23270 11111111

How is this possible if you add a condition AND cfr.modification_id = '18340300' ? You can't get modification_id = 11111111 if you only ask for modification_id = '18340300'.

So try this maybe :

SELECT cfr.id, 
       cfr.modification_id,
FROM category_filter_relations cfr
WHERE cfr.modification_id = '18340300'

UNION

SELECT cfri.id, 
       cfri.modification_id,
FROM category_filter_relations_items cfri
INNER JOIN category_filter_relations cfr ON cfr.id = cfri.category_filter_relation_id
    AND cfr.modification_id = '18340300'

This way you get the first row from category_filter_relations with column name id (the id of category_filter_relations) and modification_id (the modification_id of category_filter_relations) which is equal to '18340300' because of the condition.

The UNION now will have the column id too but now it's the id of the table category_filter_relations_items and a column modification_id that will be the modification_id of the row of category_filter_relations_items where category_filter_relation_id is the id of the table category_filter_relations which have modification_id = 18340300.

Is it what you are looking for?