SQL left join get matching records as true or false columns

user_11992235 0
Viewed 45

I have 2 tables - items table and regions table as below.

item_id
1
2
3
4
5
item_id region region_id
1 UK A
1 NA B
2 UK A
2 MX C
3 SA D
3 UK A
4 MX C
4 SA D
5 IT E
6 IN F
6 CN H

I want the result to look like this :

item_id A B C D E
1 TRUE TRUE FALSE FALSE FALSE
2 TRUE FALSE TRUE FALSE FALSE
3 TRUE FALSE FALSE TRUE FALSE
4 FALSE FALSE TRUE TRUE FALSE
5 FALSE FALSE FALSE FALSE TRUE
6 FALSE FALSE FALSE FALSE FALSE

So far I have written the below SQL and the result looks like the below table. Please guide me in how to get the desired result as shown above.

Select
ho.item_id,
Case when ho.item_id = rgn.item_id and rgn.region_id ='A' then FALSE Else TRUE end as UK,
Case When ho.item_id = rgn.item_id and rgn.region_id ='B' then FALSE ELSE TRUE end as NA ,
Case When ho.item_id = rgn.item_id and rgn.region_id ='C' then FALSE ELSE TRUE end as MX,
Case When ho.item_id = rgn.item_id and rgn.region_id ='D' then FALSE ELSE TRUE end as SA,
Case When ho.item_id = rgn.item_id and rgn.region_id ='E' then FALSE ELSE TRUE end as IT

from
    item_table ho
    left join region_table as rgn on ho.item_id = rgn.item_id
where
    rgn.region_id in (
        'A',
        'B',
        'C',
        'D',
        'E'
    ) order by ho.item_id desc
;

Result comes like this, where item ID is repeated multiple times, how to get only 1 entry for each item with corresponding TRUE or FALSE in columns.

item_id A B C D E
1 TRUE FALSE FALSE FALSE FALSE
1 FALSE TRUE FALSE FALSE FALSE
2 TRUE FALSE FALSE FALSE FALSE
3 FALSE FALSE TRUE FALSE FALSE
3 TRUE FALSE FALSE FALSE FALSE
3 FALSE FALSE FALSE TRUE FALSE
4 FALSE FALSE TRUE FALSE FALSE
4 FALSE FALSE FALSE TRUE FALSE
5 FALSE FALSE FALSE FALSE TRUE
6 FALSE FALSE FALSE FALSE FALSE
postgresql sql
2 Answers

One option is :

  • putting "TRUE" value when the region matches the column, NULL otherwise
  • aggregating the NULL values with the MAX function, so that the regions with no "region_id" corresponding to the field, will have the NULL anyways
  • changing NULL values to "FALSE" using the COALESCE function
SELECT item_id,
       COALESCE(MAX(CASE WHEN region_id = 'A' THEN 'TRUE' END), 'FALSE') AS A,
       COALESCE(MAX(CASE WHEN region_id = 'B' THEN 'TRUE' END), 'FALSE') AS B,
       COALESCE(MAX(CASE WHEN region_id = 'C' THEN 'TRUE' END), 'FALSE') AS C,
       COALESCE(MAX(CASE WHEN region_id = 'D' THEN 'TRUE' END), 'FALSE') AS D,
       COALESCE(MAX(CASE WHEN region_id = 'E' THEN 'TRUE' END), 'FALSE') AS E
FROM regions
GROUP BY item_id

If you don't want item_id = 6, then add a LEFT JOIN between the "items" table and this table.

Check the demo here.

user_124928900

If you want only items, that have entries in region table, then no need to join table with items. Aggregate function bool_or will be helpful:

Select
  item_id,
  bool_or(region_id ='A') as UK,
  bool_or(region_id ='B') as NA,
  bool_or(region_id ='C') as MX,
  bool_or(region_id ='D') as  SA,
  bool_or(region_id ='E') as  IT
from region_table 
where
    region_id in (
        'A',
        'B',
        'C',
        'D',
        'E'
    ) group by item_id order by item_id desc;
user_190463400
Related