SQL: join to the same table with a different where clause
Ok, so the problem I ran into is this: I have a table with three 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 and 3 for all ids, the return should be like this:
ID | 1 | 2
================
1 | ab | ef
2 | gh | kl
Thus, each row of ID 1 contains values for keys 1 and 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.
+2
a source to share
4 answers
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 previous versions of Oracle, replace CASE with the DECODE syntax . Oracle did not add PIVOT syntax until 11g.
+4
a source to share
Without using pivot queries, you can also join a subquery like below:
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)
+1
a source to share