where clause uses some rows from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

where clause uses some rows from a table

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-13 : 10:54:01

Hi there,

table1 has following cols:
Type------Code
EP3-------110
VP5-------210
.
.
.about hundred rows.

table2 needs get rows from table1 in its where clause as:

select * from table2
where type='EP3' and code='110'

How can I make this happen? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 11:01:07

[code]
select * from table2 t
where exists (select 1 from table1 where Type = t.Type and code = t.Code)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-13 : 11:33:13

Another way as a join:

SELECT 
    T2.* 
FROM 
    table2 AS T2
INNER JOIN
    table1 AS T1
    ON T1.Type = T2.Type 
    AND T1.code = T2.Code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 11:35:59

quote:

Originally posted by Lamprey

Another way as a join:

SELECT 
    T2.* 
FROM 
    table2 AS T2
INNER JOIN
    table1 AS T1
    ON T1.Type = T2.Type 
    AND T1.code = T2.Code

Might not get the same result if relation is one to many from table2 to table1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-13 : 13:26:43

True

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources