CODEWITHSUNDEEP
sqlsqlite
chacham15
chacham15

Reputation: 14281

Is there a way to select rows which is in a list of pairs in SQL?

I have a table FOO with the following definition:


id   INTEGER,
type INTEGER,
data VARCHAR(32),
PRIMARY KEY(id, type)

I am given a list of pairs: (id, type) and I want to get all of the data which corresponds to those pairs (along with the id and type so I know what belongs to what). Can I do this in one query? (My specific dialect of SQL is SQLite)

Upvotes: 2

Views: 643

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112712

If you have many pairs, you could also have a second table holding the pairs.

select
    foo.*
from
    foo
    inner join pairs
        on foo.id = pairs.id and foo.type = pairs.type

Upvotes: 0

Eric
Eric

Reputation: 95203

The only way to do this in SQLite is to do a bunch of or statements in a where clause. Or, you can do this:

select
    f1.*
from
    foo f1
    inner join (
        select 'id1' as id, 'type1' as type union all
        select 'id2', 'type2' union all
        select 'id3', 'type3'
    ) f2 on
        f1.id = f2.id
        and f1.type = f2.type

For edification, the where clause approach is:

select
    *
from
    foo
where
    (id = 'id1' and type = 'type1')
    or (id = 'id2' and type = 'type2')
    or (id = 'id3' and type = 'type3')

Upvotes: 2

Related Questions