0

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'
        )
Shubham Srivastava
  • 1
  • 1
  • 1
    I removed the inconsistent database tags. Please tag only with the database you are really using. – Gordon Linoff Mar 18 '21 at 14:10
  • *"I got the below but my column1 has 40 different value"* What do you mean by this? Do you mean your table has 40 rows , and `column1` has a different value on each? – Thom A Mar 18 '21 at 14:12
  • Does this answer your question? [sql query to return differences between two tables](https://stackoverflow.com/questions/2077807/sql-query-to-return-differences-between-two-tables) – Connor Low Mar 18 '21 at 15:00
  • I have provided the edits. Unfortunately still didnt get the expected answer – Shubham Srivastava Mar 18 '21 at 16:26

1 Answers1

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I also needed col2 value in the result like (1A,1B,2C.....). Is there a way we can achieve all possible combination without passing a,b,c value – Shubham Srivastava Mar 18 '21 at 16:23