IN query 1

[jimberger]

Hello,

I have a query

SELECT * FROM table where name IN (SELECT name from nametable where year=2007)

however i want to be able to do something like this

SELECT * FROM table where name AND description IN (SELECT name, description from nametable where year=2007)

but i get a syntax error

any ideas?

thanks

 

[AlexCuse]

Each subquery in an IN clause can only return one column, so you'd be looking for this:

[COLOR=blue]select[/color] * 
[COLOR=blue]from[/color] [COLOR=blue]table[/color]
[COLOR=blue]where[/color] [COLOR=blue]name[/color] in ([COLOR=blue]select[/color] [COLOR=blue]name[/color] [COLOR=blue]from[/color] nametable [COLOR=blue]where[/color] [COLOR=#FF00FF]year[/color] = 2007)
and description in ([COLOR=blue]select[/color] description [COLOR=blue]from[/color] nametable [COLOR=blue]where[/color] [COLOR=#FF00FF]year[/color] = 2007)

However, using an INNER JOIN to a subquery provides a much more attractive alternative (which I would use):

[COLOR=blue]select[/color] a.*
[COLOR=blue]from[/color] [COLOR=blue]table[/color] a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] [COLOR=blue]name[/color]
, description 
[COLOR=blue]from[/color] nametable [COLOR=blue]where[/color] [COLOR=#FF00FF]year[/color]=2007
) b
[COLOR=blue]on[/color] a.name = b.name
and a.description = b.description

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[jimberger]

ah yes!! thanks!

 

[fredericofonseca]

If using DB2 v7 or greater the IN clause can return more than one columns. maybe not ANSI SQL..


sample.


select xxx
from yyy
where (f1, f2, f3) in (select a1, a2, a3 from zzz)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[r937]

frederico, those are called row constructors and i believe that yes, they are part of standard sql

r937.com | rudy.ca