WHERE multi columns IN SELECT(multi columns)

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

SQL Server 2000 Forums

Transact-SQL (2000)

WHERE multi columns IN SELECT(multi columns)

Author

Topic

Masum7
Starting Member

33 Posts

Posted - 2007-07-26 : 10:41:57

can i select multiple columns in a select subquery and use it in WHERE clause?
for example

select tab1.a, tab1.b
from tab1
where (tab1.a, tab1.b )
in (select tab2.aa, tab2.bb from tab2)

Masum

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-26 : 10:44:09

[code]SELECT tab1.a, tab1.b
FROM tab1
WHERE EXISTS
(
SELECT *
FROM tab2
WHERE tab2.aa = tab1.a
AND tab2.bb = tab1.b
)[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 11:49:39

Or use a INNER JOIN if you know the combination of a and b is unique.

E 12°55'05.76"
N 56°04'39.42"

Masum7
Starting Member

33 Posts

Posted - 2007-07-26 : 11:58:12

Hi KH..
thnx for ur help..
but this is NOT what i want..

i want rows of tab1 where a and b both matches with aa and bb respectively of tab2

but in ur query, if it find one match that it will select all rows from tab1

quote:
Originally posted by khtan

SELECT tab1.a, tab1.b
FROM tab1
WHERE EXISTS
(
SELECT *
FROM tab2
WHERE tab2.aa = tab1.a
AND tab2.bb = tab1.b
)

KH
[spoiler]Time is always against us[/spoiler]

Masum

Masum7
Starting Member

33 Posts

Posted - 2007-07-26 : 12:03:20

hi peso..
the combination of a and b are not uniq

quote:
Originally posted by Peso

Or use a INNER JOIN if you know the combination of a and b is unique.

E 12°55'05.76"
N 56°04'39.42"

Masum

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 12:03:44

Have you ACTUALLY TRIED the query before dismissing it?

E 12°55'05.76"
N 56°04'39.42"

Masum7
Starting Member

33 Posts

Posted - 2007-07-26 : 19:32:15

hello peso, what do u mean?
i cant understand sorry..

quote:
Originally posted by Peso

Have you ACTUALLY TRIED the query before dismissing it?

E 12°55'05.76"
N 56°04'39.42"

Masum

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 19:44:50

[code]-- Prepare sample data
DECLARE @Sample TABLE (a INT, b INT)

INSERT @Sample
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 4, 4 UNION ALL
SELECT 7, 2 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 2, 3

DECLARE @Reference TABLE (a INT, b INT)

INSERT @Reference
SELECT 1, 3 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 4, 4 UNION ALL
SELECT 2, 1

-- Show the expected output
SELECT s.a,
s.b
FROM @Sample AS s
WHERE EXISTS (
SELECT *
FROM @Reference AS r
WHERE r.a = s.a
AND r.b = s.b
)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 19:45:53

The algorithm above provides

a   b
--  --
 4   4
 2   3

which is exactly what you asked for.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources