Can't get this query to work (left outer join) Postgres I've got this query I'd like to get working. I'm trying to count all the instances of a foreign key after a certain date that isn't null, and then left join that to the distinct of the original table, so I can see the variables with no entries. Here's my query:
select fulllist.fk_fc_id 
from 
(
    select distinct fk_fc_id 
    from data_item
) as fulllist 
left outer join
(
    select temp.fk_fc_id, count(*) as number 
    from 
    (
        select * 
        from data_item 
        where di_item_value not like 'Null' 
            and di_timestamp &gt; '2013-11-01 00:00:00'
    )   as temp 
    group by fk_fc_id
)   as lj
    on lj.fc_fk_id=fulllist.fk_fc_id;

the error is

ERROR:  column lj.fc_fk_id does not exist
LINE 19:  on lj.fc_fk_id=fulllist.fk_fc_id;
It looks like this is just a typo.  Your subquery is returning fk_fc_id but your join is referencing fc_fk_id.  It seems like you just need to alter the query:

select fulllist.fk_fc_id 
from 
(
    select distinct fk_fc_id 
    from data_item
) as fulllist 
left outer join
(
    select temp.fk_fc_id, count(*) as number 
    from 
    (
        select * 
        from data_item 
        where di_item_value not like 'Null' 
            and di_timestamp &gt; '2013-11-01 00:00:00'
    )   as temp 
    group by fk_fc_id
)   as lj
    on lj.fk_fc_id=fulllist.fk_fc_id;


It also seems like you would want to include the number in the final select:

select fulllist.fk_fc_id, coalesce(lj.number, 0) number
from 
(
    select distinct fk_fc_id 
    from data_item
) as fulllist 
left outer join
(
    select temp.fk_fc_id, count(*) as number 
    from 
    (
        select * 
        from data_item 
        where di_item_value not like 'Null' 
            and di_timestamp &gt; '2013-11-01 00:00:00'
    )   as temp 
    group by fk_fc_id
)   as lj
    on lj.fk_fc_id=fulllist.fk_fc_id;