multi column subquery equivalent for 7.0/2000

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

SQL Server Development (2000)

multi column subquery equivalent for 7.0/2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-09 : 09:35:05

Jeff writes "Greetings,

Can someone point me to an equivalent statement that will run on both ms sql server 7 and 2000?

I am trying to select some rows from a table where two column entries match some other criteria. The Oracle equivalent is at the bottom of this message... In MS SQL Server Query Analyzer I am getting errors trying this:

SELECT table1.col1, table1.col2, table1.col3
FROM table1
WHERE (table1.num, table1.code) in
(SELECT table2.num, table2.code from table2
where table2.col4 = value

The error is: "Incorrect Syntax near ',' ", so it seems to not like the comma in "where (table1,num, table2.code) in"

I read the manual, but just found a blurb about MS wanting a single value.. I don't want to concatenate the columns as that would not give me the same results.

I read the faq, and searched the site, but did not see this (yes I could be blind).

Thanks for any assistance,
Jeff

select (col1, col2, col3
from table1
where (num, code) in
(select num, code from table2 where col4 = value);"

Nazim
A custom title

1408 Posts

Posted - 2002-05-09 : 09:46:58

Look season of Sql Server bashing

SELECT table1.col1, table1.col2, table1.col3
FROM table1 WHERE table1.num in
(SELECT table2.num, table2.code from table2
where table2.col4 = value )
or code in ((SELECT table2.code from table2
where table2.col4 = value )

or
SELECT table1.col1, table1.col2, table1.col3
FROM table1
inner join (SELECT table2.num, table2.code from table2
where table2.col4 = value ) t2
on table1.num=t2.num or table1.code=t2.code

HTH

--------------------------------------------------------------

Edited by - Nazim on 05/09/2002 11:33:54

kishj
Starting Member

1 Post

Posted - 2002-05-09 : 10:19:45

Thanks much for your reply.

I ended up using this (inner join?):
select table1.col1, table1.col2,table1.col3
from table1,table2
where ((table1.code = table2.code) and
(table1.num = table2.num) and
(table2.col4 = value))

Jeff

Nazim
A custom title

1408 Posts

Posted - 2002-05-09 : 10:29:29

Today Am little weird. NOT in my senses. Sorry for that Jeff

SELECT table1.col1, table1.col2, table1.col3
FROM table1
inner join (SELECT table2.num, table2.code from table2
where table2.col4 = value ) t2
on table1.num=t2.num or table1.code=t2.code

--------------------------------------------------------------

Edited by - Nazim on 05/09/2002 10:35:53

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-09 : 11:06:52

Or, even a simple INNER JOIN:

SELECT table1.col1, table1.col2, table1.col3
FROM table1 INNER JOIN table2 ON table1.num=table2.num or table1.code=table2.code
WHERE table2.col4 = value

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources