CODEWITHSUNDEEP
sqlpostgresqlexists
user1389960
user1389960

Reputation: 433

Subsetting a table by multiple columns in another table

From Table A below, I would like to select the rows where A2 = 1 and where the A1 - A3 combination exists as a B1 - B2 combination in Table B below. The result should be Table C. How do I do that? My actual tables are large so efficiency is important. Thank you.

Table A

A1   |A2  |A3  |A4
-----+----+----+----
a    |1   |9   |o   
b    |1   |10  |k
c    |0   |2   |d   
d    |0   |12  |f
e    |1   |8   |g   
f    |1   |14  |h
e    |1   |12  |p

Table B

B1   |B2  |B3  
-----+----+----
a    |9   |k    
a    |9   |l  
c    |2   |m   
e    |8   |o  
c    |3   |p   
e    |8   |q

Table C

A1   |A2  |A3  |A4
-----+----+----+----
a    |1   |9   |o   
e    |1   |8   |g

Upvotes: 0

Views: 25

Answers (1)

McNets
McNets

Reputation: 10807

select A1, A2, A3, A4
from   tableA
where  A2 = 1
and    exists (select 1
               from   tableB
               where  B1 = A1
               and    B2 = A3);

Depending on your table schema:

select     A1, A2, A3, A4
from       tableA
inner join tablaB
on         A1 = B1
and        A3 = B2
where      A2 = 1;

Upvotes: 1

Related Questions