Question 89081 in PostgreSQL Question posted in
The official documentation can be found here.

How to detect whether the value exists or not in each table – Postgresql

I would like to detect whether value is exists or not in each table.I have following table

tableA

code value
a    1
b    2
c    3

tableB

code value
a    4
c    5 
d    6

tableC

code value
e    10  
f    11
g    12

I want to set variable. and If I set value like value = 'a'.My desired result is like follows.

value = a exists in tableA so that the column named tableA is set 1 and in tableC value = a doesn’t exist so that value 0 was set.

code tableA tableB tableC valueA valueB valueC
a    1      1      0      1      4      null

I tried like follows, but I couldn’t figure out how to join another table.

EXISTS (SELECT 1 FROM tableA WHERE code = 'a')

Are there any smarter ways to achieve this?
If someone has opinion, will you please let me know
Thanks

Tags:

3

Answers


  1. 0

    Assuming the code is unique on each table.

    You can do it using full outer join to returns all records when there is a match in left (tableA) or right (tableB) :

    Select coalesce(a.code, b.code, c.code) as code,
  MAX(case when a.code is not null then 1 else 0 end)  as tableA,
  MAX(case when b.code is not null then 1 else 0 end)  as tableB,
  MAX(case when c.code is not null then 1 else 0 end)  as tableC,
  MAX(coalesce(a.value,0))  as valueA,
  MAX(coalesce(b.value,0))  as valueB,
  MAX(coalesce(c.value,0)) as valueC
from tableA a
full outer join TableB b on a.code = b.code
full outer join TableC c on c.code = b.code
where a.code = 'a' or b.code = 'a' or c.code = 'a'
group by coalesce(a.code, b.code, c.code)

    Or using USING instead of ON on full outer join :

    Select coalesce(a.code, b.code, c.code) as code,
  case when a.code is not null then 1 else 0 end as tableA,
  case when b.code is not null then 1 else 0 end as tableB,
  case when c.code is not null then 1 else 0 end as tableC,
  coalesce(a.value,0) as valueA,
  coalesce(b.value,0) as valueB,
  coalesce(c.value,0) as valueC
from tableA a
full outer join TableB b using (code)
full outer join TableC c using (code)
where a.code = 'a' or b.code = 'a' or c.code = 'a'

    Result :

    code    tablea  tableb  tablec  valuea  valueb  valuec
a       1       1       0       1       4       null

    Demo here

    Login or Signup to reply.
  2. 0

    SelVazis answer is good and clean. however it will not return a result row if the value is not found in any of the tables. And multiple result rows if the value is in one or more tables more then once. Therefore it is better to first make a reponse table and look the answer up in that response table:

    select 
    req.code, 
    resp."tableA", 
    resp."tableB", 
    resp."tableC", 
    resp."valueA", 
    resp."valueB", 
    resp."valueC" 
from 
    (select 'a' as code) req 
left join
    (Select 
        coalesce(codeA, codeB, codeC) as code,
        max(case when codeA is not null then 1 else 0 end) as "tableA",
        max(case when codeB is not null then 1 else 0 end) as "tableB",
        max(case when codeC is not null then 1 else 0 end) as "tableC",
        string_agg(distinct valueA, ', ') as "valueA",
        string_agg(distinct valueB, ', ') as "valueB",
        string_agg(distinct valueC, ', ') as "valueC"
    from 
        (select 
            * 
        from 
            (select code as codeA, value as valueA from tableA) a
        full outer join 
            (select code as codeB, value as valueB from tableB) b 
        on a.codeA = b.codeB
        full outer join 
            (select code as codeC, value as valueC from tableC) c 
        on c.codeC = b.codeB) 
        as subresp 
    group by 
        coalesce(codeA, codeB, codeC)
   ) resp 
   on req.code = resp.code

    In this case i asume you want all values returned from the tables, so I string aggregated them. Feel free to replace that with min, max or whatever floats your boat.

    DB fiddle for completeness here

    Login or Signup to reply.
  3. 0

    I’d probably just join the three data sets you are looking for. You can do this with pseudo full outer joins (as there is no real join criteria) or with a cross join of aggregations:

    with a as (select min(value) as val from tablea where code = 'a')
   , b as (select min(value) as val from tableb where code = 'a')
   , c as (select min(value) as val from tablec where code = 'a')
select
  a.val is not null as code_exists_in_a,
  b.val is not null as code_exists_in_b,
  c.val is not null as code_exists_in_c,
  a.val as value_in_a,
  b.val as value_in_b,
  c.val as value_in_c
from a cross join b cross join c;

    (For the full outer joins you could replace select min(value) as val by select code, value as val and from a cross join b cross join c by from a full outer join b using (code) full outer join c using (code)).

    Disclaimer: If the value can be null in the tables and you would want to show this as value exists, but value is null, then the above query would have to be adjusted for this.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search