CODEWITHSUNDEEP
sql
Steeve
Steeve

Reputation: 1

Partial match on key composed of two columns

Let s say my two tables keys are comprised of column A and B:

Table 1 

Column A Column B
1                1
1                X
2                2
3                3

Table 2

Column A Column B
1                1
2                2
3                3

How do I select only the rows in Table1 where the key only matches partially. My intended result would pulling out row:

Column A Column B
1               X

Basically finding all rows where Column A and B match but where following records were ´left out' in Table 2

Upvotes: 0

Views: 128

Answers (3)

Quote
Quote

Reputation: 41

You can solve this problem using NOT-EXISTS subquery:

SELECT
  input1.ColumnA,input1.ColumnB
FROM
  t1
WHERE
  NOT EXIST (
    SELECT *
    FROM t2
    WHERE t1.ColumnB = t2.ColumnB AND t1.ColumnA = t2.ColumnA)

Explanation: you select rows in table1 whose values are not contained in table2.

Upvotes: 0

Imbar M.
Imbar M.

Reputation: 1114

join the tables on "partial" match

select t1.*
from table1 t1
join table2 t2 on ((t1.a = t2.a and t1.b <> t2.b) or (t1.a <> t2.a and t1.b = t2.b))

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

select * 
from tabl1
join tabl2
on (tabl1.cola == tabl1.cola and tabl1.colb <> tabl1.colb)
or (tabl1.cola <> tabl1.cola and tabl1.colb == tabl1.colb)

Upvotes: 1

Related Questions