SQL: join within same table with different 'where' clause

Question

Ok, so the problem I'm facing is this, I have a table with 3 columns : ID, Key and Value.

ID | Key | Value
================
1  |  1  |  ab
1  |  2  |  cd
1  |  3  |  ef
2  |  1  |  gh
2  |  2  |  ij
2  |  3  |  kl

Now I want to select the value of Keys 1 & 3 for all IDs, the return should be like this

ID |  1  | 2
================
1  |  ab |  ef
2  |  gh |  kl

So per ID 1 row containing the Values for Keys 1 & 3.

I tried using 'join' but since I need to use multiple where clauses I can't figure out how to get this to work ..

Answer 1

Why can't you just do three queries?

If I understand you correctly, your going to have to use a union join.

Answer 2

For Oracle 8i+, use:

  SELECT t.id,
         MAX(CASE WHEN t.key = 1 THEN t.value ELSE NULL END) AS "1",
         MAX(CASE WHEN t.key = 2 THEN t.value ELSE NULL END) AS "2"
    FROM TABLE t
GROUP BY t.id

For Oracle versions prior, swap the CASE out for DECODE syntax. Oracle didn't add the PIVOT syntax until 11g.

Answer 3

SELECT ID, VALUE AS v1, S.v2
FROM TABLE
WHERE KEY = 1
JOIN (SELECT ID, VALUE AS v2 FROM TABLE WHERE Key =3) AS S ON TABLE.ID = S.ID

If you need more, add a join for each where...

Answer 4

Without using pivot queries, you could also join with a subquery, as follows:

SELECT     t.id, MAX(key_1.value) AS '1', MAX(key_3.value) AS '2'
FROM       tb t
INNER JOIN (SELECT id, value FROM tb WHERE `key` = 1) key_1 ON (key_1.id = t.id)
INNER JOIN (SELECT id, value FROM tb WHERE `key` = 3) key_3 ON (key_3.id = t.id)
GROUP BY   t.id;

Test Case (in MySQL):

CREATE TABLE tb (`id` int, `key` int, `value` char(2));
INSERT INTO tb VALUES (1, 1, 'ab');
INSERT INTO tb VALUES (1, 2, 'cd');
INSERT INTO tb VALUES (1, 3, 'ef');
INSERT INTO tb VALUES (2, 1, 'gh');
INSERT INTO tb VALUES (2, 2, 'ij');
INSERT INTO tb VALUES (2, 3, 'kl');

Result:

+------+------+------+
| id   | 1    | 2    |
+------+------+------+
|    1 | ab   | ef   |
|    2 | gh   | kl   |
+------+------+------+
2 rows in set (0.04 sec)