ppardoz ppardoz - 2 years ago 66
SQL Question

Select from table with two conditions

I'm stuck with a query and I'd need some help.

I need to select values from a table which meets two conditions from other table, for example:

Select * from table1
where ID = (select ID from table2)
AND value = (select value from table2)

So, if I'd need only one value from the table, I could query:

Select * from table1 where ID = (id1) AND value = (value1)

The only solution that I know is using IN, but it wouldn't be the requested solution.

I need something similar to this, but counting that the data returned by table2 is not only one row, but multiple.

Could somebody give me some clue on how to find this?


Answer Source

One method uses exists:

Select *
from table1 t1
where exists (select 1
              from table2 t2
              where t2.id = t1.id and t2.value = t1.value

This is ANSI standard syntax, so it should work in any database. Some databases support this syntax:

select t1.*
from table1 t1
where (t1.id, t1.value) in (select t2.id, t2.value from table2 t2);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download