[Lack of memory] Left join on the same table

Question

Hi guys, i dont remember how to join a table to itself.. my table is:

| id | proc | value | kind |
| 1  | 1    | foo   | a    |
| 2  | 1    | bar   | b    |
| 3  | 2    | some  | a    |

And i need to retrieve the value col where proc is $proc and kind is both 'a' and 'b'.. well, i need to do have that (looking for proc = 1):

| v_a | v_b |
| foo | bar |

So i wrote this query:

SELECT
    a.value AS v_a,
    b.value AS v_b
FROM
    (SELECT value FROM table WHERE proc = '1' AND kind = 'a') AS a,
    (SELECT value FROM table WHERE proc = '1' AND kind = 'b') AS b

And works but only if in the table i have both rows for kind=a and kind=b.

But i need that if miss a row, i'll have a null value: if i look for proc=2 i must get:

| v_a | v_b |
| foo | NULL|

Instead, with my query i dont get anythong if the b or a row is missing.

Im working with mysql...How to do that?

Edit: I could use the UNION clause, but this wont allow me to have a NULL value when one row is missing (kind=a or kind=b)

Answer 1

select * from t1 full outer join t2 on some_id = some_other_id?

Answer 2

I missed your need to retrieve NULLs. This is complicated, but it seems to work:

SELECT
    a.value AS a_val,
    (SELECT value FROM t b2 WHERE b2.proc = a.proc AND b2.id != a.id) AS b_val
FROM `t` a
WHERE a.kind = 'a'

UNION DISTINCT

SELECT
    (SELECT value FROM t a2 WHERE a2.proc = b.proc AND a2.id != b.id) AS a_val,
    b.value AS b_val
FROM `t` b
WHERE b.kind = 'b'

Answer 3

you have to do a full join in case proc exists for 'a' but not for 'b' or vice versa:

SELECT
    a.value v_a, 
    b.value v_b
FROM (SELECT proc, value FROM tab WHERE kind = 'a') a LEFT JOIN 
(SELECT proc, value FROM tab WHERE kind = 'b') b
  ON a.proc = b.proc
WHERE a.proc = '1'
UNION
SELECT
    a.value v_a, 
    b.value v_b
FROM (SELECT proc, value FROM tab WHERE kind = 'b') b LEFT JOIN 
(SELECT proc, value FROM tab WHERE kind = 'a') a
  ON a.proc = b.proc
WHERE b.proc = '1'

EDIT: 1st a gave MS SQL Server query (FULL JOIN) but apparently it's not supported by MYSQL, so I changed it to a UNION of 2 LEFT JOIN

Answer 4

SELECT
    a.value AS v_a,
    b.value AS v_b
FROM
    (SELECT proc, value FROM table WHERE proc = '1' AND kind = 'a') AS a
LEFT OUTER JOIN
    (SELECT proc, value FROM table WHERE proc = '1' AND kind = 'b') AS b
ON a.proc=b.proc