How to create a subset query in sql?

Question

I have two tables as follows:

CREATE List (
    id   INTEGER,
    type INTEGER REFERENCES Types(id),
    data TEXT,
    PRIMARY_KEY(id, type)
);

CREATE Types (
    id   INTEGER PRIMARY KEY,
    name TEXT
);

Now I want to create a query that determines all ids of List which has given type strings.

For example,

List:
1    0    "Some text"
1    1    "Moar text"
2    0    "Foo"
3    1    "Bar"
3    2    "BarBaz"
4    0    "Baz"
4    1    "FooBar"
4    2    "FooBarBaz"

Types:
0    "Key1"
1    "Key2"
2    "Key3"

Given the input "Key1", "Key2", the query should return 1, 4.

Given the input "Key2", "Key3", the query should return 3, 4.

Given the input "Key2", the query should return 1, 3, 4.

Thanks!

Are you looking for SQL that will work in (almost) all DBMS or for a specific one? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 4, 2012 at 20:05
@ypercube I was looking for a general solution, sorry for not specifying — chacham15
– chacham15, Commented Aug 4, 2012 at 20:17

juergen d · Accepted Answer · 2012-08-04 20:31:45Z

5

select distinct l.id 
from list l
inner join types t on t.id = l.type
where t.name in ('key1', 'key2')
group by l.id
having count(distinct t.id) = 2

You have to adjust the having clause to the number of keys you are putting in your where clause. Example for just one key:

select distinct l.id 
from list l
inner join types t on t.id = l.type
where t.name in ('key2')
group by l.id
having count(distinct t.id) = 1

SQlFiddle example

edited Aug 4, 2012 at 20:31

answered Aug 4, 2012 at 19:53

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

juergen d Over a year ago

See the last sentence in my answer for that.

ypercubeᵀᴹ Over a year ago

@jurgen: Why the >= and not = ?

juergen d Over a year ago

@ypercube: At 2nd thought using distinct count and the where clause -- there is no need for >=.

ypercubeᵀᴹ Over a year ago

You don't even have to use the DISTINCT t.id because of the PRIMARY_KEY(id, type)

Gordon Linoff · Accepted Answer · 2012-08-04 20:05:39Z

2

You can use the following trick to extend Jurgen's idea:

with keys as (
    select distinct t.id
    from types t
    where t.name in ('key1', 'key2')
)
select l.id 
from list l join
     keys k
     on l.type = keys.id cross join
     (select count(*) as keycnt from keys) k
group by l.id
having count(t.id) = max(k.keycnt)

That is, calculate the matching keys in a subquery, and then use this for the counts. This way, you only have to change one line to put in key values, and you can have as many keys as you would like. (Just as a note, I haven't tested this SQL so I apologize for any syntax errors.)

answered Aug 4, 2012 at 20:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

chacham15 Over a year ago

+1 for the effort, but i dont think that sqlite supports this type of syntax

ypercubeᵀᴹ Over a year ago

@chacham15: Your question has no mention of SQLite. ANd the CTE (the with part) can easily be moved into the subquery (but you'll have to write the key-list twice)

chacham15 Over a year ago

@ypercube you're right, i didnt mention it because I want a cross SQL compatible solution. but i recognized the solution and so gave it +1

Community · Accepted Answer · 2017-05-23 11:51:58Z

1

If you can dynamically produce the SQL, this may be one of the most efficent ways, in many DBMS:

SELECT l.id
FROM   List  l
  JOIN Types t1  ON t1.id = l.type
  JOIN Types t2  ON t2.id = l.type
WHERE  t1.name = 'Key1'
  AND  t2.name = 'Key2' ;

See this similar question, with more than 10 ways to get the same result, plus some benchmarks (for Postgres): How to filter SQL results in a has-many-through relation

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Aug 4, 2012 at 20:17

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

4 Comments

chacham15 Over a year ago

+1 This works, but is less efficient and more difficult to construct than the other answers

ypercubeᵀᴹ Over a year ago

Actually, it will usually be more efficient than the solutions with GROUP BY. But yes, more difficult to construct. It's not a generic query.

ypercubeᵀᴹ Over a year ago

@chacham15: Perhaps you can make some performance tests with SQLite and tell us the results.

chacham15 Over a year ago

I am really tempted to try and see. The only problem is that I dont have an example database to test it out on yet :(. Thanks for that link, it was interesting to see those results and I'll definitely keep it in mind when I do finally fill up the database.

Collectives™ on Stack Overflow

How to create a subset query in sql?

3 Answers 3

4 Comments

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related