SQL Full Outer Join on same column in same table

Question

This may be more of a design issue than anything, but I'm hoping it's possible without too much voodoo.

Suppose I have a table like this:

SELECT * FROM stuff;

 id   grp
  1     a
  2     a
  3     a
  1     b
  2     b
  4     b

And I want to get something like this (with the ID's grouped in columns):

a.id    b.id
   1       1
   2       2
   3    null
null       4

Is this possible? I've tried the following query...

SELECT a.id, b.id FROM stuff a
FULL OUTER JOIN stuff b ON a.id = b.id
WHERE a.grp = 'a' AND b.grp = 'b';

... but I only get the common nodes:

a.id    b.id
   1       1
   2       2

I've also tried playing around with the JOIN ON and WHERE but can't seem to get the desired result.

The closest question I could find online is this one, but I couldn't get UNION to work either.

Performance isn't an issue, and I can assume there are only 2 groups.

Any ideas?

Answer 1

You're doing the wrong thing first, and attempting to fix it up afterwards. That's not going to work.

The things you want to join are select * from stuff where grp = 'a' and select * from stuff where grp = 'b'. So join those:

select a.ID as a, b.ID as b from
  (select * from stuff where grp = 'a') a
full join
  (select * from stuff where grp = 'b') b
on b.id = a.id

SQL Fiddle