CODEWITHSUNDEEP
sqloracle-databasepivot
Pmarcoen
Pmarcoen

Reputation: 1236

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

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 ..

Upvotes: 2

Views: 8759

Answers (4)

Daniel Vassallo
Daniel Vassallo

Reputation: 344431

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)

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332661

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.

Upvotes: 4

moi_meme
moi_meme

Reputation: 9318

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...

Upvotes: 0

azz0r
azz0r

Reputation: 3311

Why can't you just do three queries?

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

Upvotes: 0

Related Questions