CODEWITHSUNDEEP
sql
Mukul Varshney
Mukul Varshney

Reputation: 3141

SQL with both column values exist in another table

I have 2 tables Table T1 with 2 columns(i.e. C1 and C2) and Table T2 with 1 column (i.e. C3). I want to get only those records from T1 such that value in C1 and C2 (both together) exist in C3. Is my sql good ? Any alternatives?

Select * 
From T1
Where (T1.[C1]
        IN (Select T2.[C3]
            From T2))
      AND
      (T1.[C2]
        IN (Select T2.[C3]
            From T2))

enter image description here enter image description here

enter image description here

Upvotes: 0

Views: 25

Answers (1)

GregHNZ
GregHNZ

Reputation: 8969

Something like this should also do it.

SELECT t1.* 
FROM t1
INNER JOIN t2 AS t2_c1 ON t1.c1 = t2_c1.c3
INNER JOIN t2 AS t2_c2 ON t1.c2 = t2_c2.c3

The inner join syntax means return rows only where the specified fields match.

I've added t2 twice - once for c1 and once for c2. So the as t2_c1 gives you an alias to distinguish which instance of t2 you're referring to. What the alias is doesn't matter, as long as you use the same thing when referring to that table later.

Upvotes: 1

Related Questions