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