CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Shubham Srivastava
Shubham Srivastava

Reputation: 1

sql creating result set of different mismatches of two tables based on different groups as shown in image

How to find difference of two tables based on different group: I have a below scenario:

(TABLE1
COL1=1,2,3,4,5

Table2
COL1=1,2,3,4,5
COL2=A,B,C.....
COL3=XXX OR BLANK

output
1A,1B,1C,2A,2B
)

I got the below sql but my column2 has 40 different value and it will be a big union all statement. Also the comparison needs to be done with all entries of table 1 needs to compare with table2 as A has (1,2,3 in table1 ) B has (2,3 in table2) any way to achieve it efficiently will be highly appreciated ?

Edit: Col2 value should not be hardcoded it should be distinct value from Table2, it can be from a-z, so my below union all will have 26 statement having same code just col2 will changeenter image description here

/*SQL that is working result: */

SELECT col1
FROM table1
WHERE col1 NOT IN (
        SELECT col1
        FROM table2
        WHERE col3 = 'xxx'
            AND col2 = 'A'
        )

UNION ALL

SELECT col1
FROM table1
WHERE col1 NOT IN (
        SELECT col1
        FROM table2
        WHERE col3 = 'xxx'
            AND col2 = 'B'
        )

UNION ALL

SELECT col1
FROM table1
WHERE col1 NOT IN (
        SELECT col1
        FROM table2
        WHERE col3 = 'xxx'
            AND col2 = 'C'
        )

Upvotes: 0

Views: 140

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Are you looking for logic like this? For your example all the col3s are the same, so:

SELECT t1.col1
FROM table1 t1
WHERE NOT EXISTS (SELECT 1
                  FROM table2 t2
                  WHERE t2.col1 = t1.col1 AND
                        t2.col3 = 'xxx' AND
                        t2.col1 IN ('A', 'B', 'C')
                 );

This assumes that the comparison on col3 is always 'xxx' as in your example. The logic can easily be tweaked if that is not the case.

Upvotes: 1

Related Questions