Sqlite Join (or some variant thereof)

Question

Given a resultset like this:

Col1    Col2
============
BAML    491
BARC    362
BDPT    1
BNP     9

(select Col1, count(some_col) as Col2 from Table where another_col='SomeCondition" group by Col1)

and another like this:

Col3    Col2
============
BAML    494
BARC    366
BDPT    1
BNP     10
CALY    3

(select Col3, count(some_col) as Col2 from Table where another_col='SomeOTHERCondition" group by Col3)

How do I "merge" these two queries to form:

BAML    491       494
BARC    362       366
BDPT    1         1
BNP     9         10
CALY              3

Please note that the first two queries operate on the same table. I can see some variant of Join helping here (haven't worked with sql all that much). I just can't figure out how to put those two queries into a single query to get the merged reusltset.

mu is too short mu is too short · Accepted Answer · 2011-10-04T21:02:24

Try this:

select b.col1, b.col2, a.col2
from (
    select Col3, count(some_col) as Col2
    from Table
    where another_col = 'SomeOTHERCondition'
    group by Col3
) as a
left outer join (
    select Col1, count(some_col) as Col2
    from Table
    where another_col = 'SomeCondition'
    group by Col1
) as b
on a.col3 = b.col1

I'm not sure if SQLite supports that though.

Based on the comments the above does work but you really want a FULL OUTER JOIN and SQLite doesn't like that. You could try faking it with two LEFT OUTER JOINs and a UNION though:

select b.col1, b.col2, a.col2
from ... as a left outer join ... as b ...
UNION
select b.col1, b.col2, a.col2
from ... as b left outer join ... as a ...

Sqlite Join (or some variant thereof)

1 Answers