Re: count records in two different table joined by

Hi,

Am 07.07.17 um 12:16 schrieb Patrick B:
> Hi guys!
>
> I've got 2 tables, and I need to get some data between them.
>
> test1:
>
> WITH account_status AS (
> select
> CASE
> WHEN regdate = 1 THEN 'yes'
> WHEN regdate = 2 THEN 'no'
> from test1
> end as status_a
> )
>
> select status_a from account_status group by status_a
>
> test2:
>
> WITH user_status AS (
> select
> CASE
> WHEN regdate = 1 THEN 'yes'
> WHEN regdate = 2 THEN 'no'
> from test1
> join test2 as t2 on t2.test1_id = t1.id <http://t1.id>
> end as status_a
> )
>
> select status_a from user_status group by status_a
>
>
> It works fine.. but I would like to get that data in one single
> query.. How can I do that?
>
> I'm using Postgres 9.3.
>
> Thanks!
> Patrick

one possibility is:

select distinct
case
when regdate = 1 THEN 'yes'
when regdate = 2 THEN 'no'
end as status_a
, t2.id is null as test2exists
from test1 t1 left join test2 t2 on t2.test1_id = t1.id

hth
Thomas