4

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!

chacham15
  • 13,719
  • 26
  • 104
  • 207

3 Answers3

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

juergen d
  • 201,996
  • 37
  • 293
  • 362
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.)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • +1 for the effort, but i dont think that sqlite supports this type of syntax – chacham15 Aug 04 '12 at 20:08
  • @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) – ypercubeᵀᴹ Aug 04 '12 at 20:09
  • @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 – chacham15 Aug 04 '12 at 20:12
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

Community
  • 1
  • 1
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • +1 This works, but is less efficient and more difficult to construct than the other answers – chacham15 Aug 04 '12 at 20:19
  • 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ᵀᴹ Aug 04 '12 at 20:21
  • @chacham15: Perhaps you can make some performance tests with SQLite and tell us the results. – ypercubeᵀᴹ Aug 04 '12 at 20:25
  • 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. – chacham15 Aug 04 '12 at 20:29