id IN (list of other ids) depending on IFs

I have an easy join like:

SELECT field.table1, field.table2 
LEFT JOIN table2 ON table2.table1 = table1.id
FROM table1
WHERE table1.id = [changing id];

Where table1.id is the autoincrement index and table2.table1 is the 1:n key from table1 to table2.

Now I need to do something like:

SELECT field.table1, field.table2 
LEFT JOIN table2 ON 
[   IF table1.id = 1 THEN table2.table1 IN (10, 11, 12)
    IF table1.id = 2 THEN table2.table1 IN (13, 14)
    IF table1.id = 3 THEN table2.table1 IN (15)
    IF table1.id = 4 THEN table2.table1 IN (16, 17, 18)]
FROM table1
WHERE table1.id = [changing id];

Whats the correct syntax to do so?

Please keep in mind that this is just an example. There are reasons why I need to do it this way (the connotations 1 => 10,11,12, 2 => 13,14 and so on are not coming from the DB / the hole query is dynamically fed into another system which I can’t change and where the query is done, so I do not know the id at this moment).

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The syntax should be a series of logical (AND OR) clauses:

SELECT field.table1, field.table2 
LEFT JOIN table2 ON 
   (table1.id = 1 AND table2.table1 IN (10, 11, 12) )
OR (table1.id = 2 AND table2.table1 IN (13, 14) )
OR (table1.id = 3 AND table2.table1 IN (15) ) 
FROM table1
WHERE table1.id = [changing id];

Method 2

What I would do is create a mapping table, like so (we’ll call it table3):

And then add another level for joins:

SELECT field.table1, field.table2 
FROM table1
LEFT JOIN table3 ON table3.Table1ID = table1.id
LEFT JOIN table2 ON table2.id = table3.table2ID
WHERE table1.id = [changing id];

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0