Home > Mobile >  MySQL : How to filter query using "IN" on Composite Primary Key
MySQL : How to filter query using "IN" on Composite Primary Key

Time:10-30

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
  • Related