SQL advanced sub select query

3
votes

I would like to expand on this simple sub select:

Select * from table1 where pkid in (select fkid from table2 where clause...)

The logic above is fairly simple - get me all rows in table1 where the pkid is contained in the subset returned from the sub select query that has a where clause. It works well because there is only 1 field being returned.

Now I want to expand on this.

In table 1 I want to return results where field1 and field2 and field3 in select (field1, field2, field3 from table2 where clause...)

How is this possible?

Thanks in advance.

Example.

TABLE1

FIELD1  FIELD2 FIELD3    
1       2      3    
2       3      4     
4       5      6

TABLE 2

2       3      4 
4       5      6

I want to return 2 results.

5
sql

5 Answers

6
votes

If I understand what you need you can try:

SELECT t1.field1, t1.field2, t1.field3 FROM table1 t1
INNER JOIN table2 t2
    ON t1.field1 = t2.field1
   AND t1.field2 = t2.field2
   AND t1.field3 = t2.field3
   AND t2.... // Use this as WHERE condition
1
votes

Like Marco pointed out, what you want to do is an INNER JOIN.

But (that's just FYI, you should definitely use Marco's solution) it's also possible to simply use braces.

Select * 
from table1 
where (field1, field2, field3) in (select field1, field2, field3 from table2 where clause...)

At least in MySQL (wasn't this question tagged with MySQL?)

0
votes

you can use a temporary table 

select field1, field2, field3 into #tempTable from table2 where clause...

select * from table 1 
where filed1 in (select field1 from #tempTable)
and filed2 in (select field2 from #tempTable)
and filed3 in (select field3 from #tempTable)
0
votes

Avoid using IN for most cases like this. It's very limitting.

I prefer to use a JOIN in most cases.

SELECT
  *
FROM
  yourTable
INNER JOIN
  (SELECT c1, c2, c3 FROM anotherQuery) AS filter
    ON  yourTable.c1 = filter.c1
    AND yourTable.c2 = filter.c2
    AND yourTable.c3 = filter.c3

(Ensure the filter returns unique combinations of c1, c2, c3 using DISTINCT or GROUP BY if necessary)

0
votes

you didn't mentioned engine, so I'll assume SQL Server. This query will show you what's on both tables

select FIELD1, FIELD2 from table1 
intersect
select FIELD1, FIELD2 from table2