CODEWITHSUNDEEP
sqloracle-database
c0nn
c0nn

Reputation: 327

Test for existence in a unioned list

I'm trying to test for whether items in multiple lists belong to the union of all the lists. There is a column for all of the items unioned together, and additional columns for each individual list. I want to have true or false, depending on whether each list contains the specified item. As an example, there are two tables, containing the following items:

-- Table 1
Item
apple
orange

-- Table 2
Item
pear
grape

-- Resulting Table:
Item       Table1      Table2
apple      true        false
orange     true        false
pear       false       true
grape      false       true

My SQL to generate this query is as follows:

WITH utable AS (SELECT item FROM table1 UNION
                SELECT item FROM table2)
SELECT utable.item,
       CASE WHEN EXISTS (SELECT table1.item, utable.item 
                         FROM utable LEFT JOIN table1 ON utable.item = table1.item) 
                         THEN 'true' ELSE 'false' END AS Table1,
       CASE WHEN EXISTS (SELECT table2.item, utable.item 
                         FROM utable LEFT JOIN table2 ON utable.item = table2.item) 
                         THEN 'true' ELSE 'false' END AS Table2,
FROM utable;

I am sure that my case statement doesn't work, because I don't completely understand the logic behind the syntax. It always writes true. What am I doing wrong, and do you have any suggestions for improvement? Thanks for any advice.

Upvotes: 0

Views: 30

Answers (2)

JackSanity
JackSanity

Reputation: 1

case when with subquery if you always want a result of true or false?

create table x1 (item varchar(30))
    insert into x1 (item) values ('apple')
    insert into x1 (item) values ('orange')

create table x2 (item varchar(30))
    insert into x2 (item) values ('pear')
    insert into x2 (item) values ('grape')

with utable as ( select item from x1 union 
                select item from x2)
select utable.item
    , case when item in (select item from x1) then 'True' else 'False' end as Table1
    ,case when item in (select item from x2) then 'True' else 'False' end as Table2
 from utable

Upvotes: 0

Sebas
Sebas

Reputation: 21542

What about something like this:

WITH utable AS (SELECT item, 'true' AS table1, 'false' AS table2 FROM table1 UNION
                SELECT item, 'false' AS table1, 'true' AS table2 FROM table2)
SELECT item, MAX(table1) AS table1, MAX(table2) AS table2
FROM utable
GROUP BY item
;

Upvotes: 1

Related Questions