SQL left join get matching records as true or false columns-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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;