I have a table in MySQL such the primary key is a composite one like this :
-------------- ---- ------------------------------
| fk_something | id | label |
-------------- ---- ------------------------------
| 1 | 1 | First record of something 1 |
| 2 | 1 | First record of something 2 |
| 2 | 2 | Second record of something 2 |
| 1 | 2 | Second record of something 1 |
| 2 | 3 | Third record of something 2 |
-------------- ---- ------------------------------
But now I have a tricky query to do, I want to retrieve for example the 2-1 and 1-2 records using a "IN" filter... How can I do it ?
I tried something like :
SELECT * FROM my_table WHERE id IN (1, 2) AND fk_something IN (2, 1)
But it gave me 1-1 and 2-2 records too ...
CodePudding user response:
You can take advantage of the rarely used SQL feature called row value expressions:
SELECT * FROM myTable
WHERE (id, fk_something) IN (
(1, 2),
(2, 1),
(2, 3)
)
CodePudding user response:
I think this can help you
SELECT *
FROM my_table
WHERE id IN (1, 2)
AND fk_something IN (1, 2)
AND id != fk_something