Re: [GENERAL] count records in two different table joined by

[Thomas Markus]

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