SQL SubQuery Help

Question

SQL SubQuery Help

gokrishis

Newbie

Points: 9
More actions
September 9, 2009 at 1:24 pm

#210462

Hi,
I need a SQL query that will do this:
SELECT * FROM Table1 T1
WHERE T1.column1 IN (SELECT column1 FROM Table2)
AND T1.column2 IN (SELECT column2 FROM Table2)
eg.
SELECT * FROM Employees T1
WHERE T1.Name IN (SELECT Name FROM Students)
AND T1.Age IN (SELECT Age FROM Students)
Basically, both of inner queries do the same thing but return different column values. What would be the best way to write this query?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

[Edit] Read that a bit too quickly the first time.

The way you are doing it should work. Perhaps if you share your end goal, we can come up with a different way of going about getting there.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

The current query (either one) will return all employees who's name and age are in the student table.... they just might not be on the same row, though because there is no correlation between the two sub queries via the outer query.

Why wouldn't you just use a single inner join with two criteria for this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 3

Like this...

SELECT e.*

FROM dbo.Employees e

INNER JOIN dbo.Students s

ON e.Name = s.Name

AND e.Age = s.Age

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 4

Jeff Moden (9/9/2009)
Like this...
SELECT e.*
FROM dbo.Employees e
INNER JOIN dbo.Students s
ON e.Name = s.Name
AND e.Age = s.Age

That's what I edited back out when I reread it. Should have left it tho, maybe the lack of correlation was unintentional, as you suggest.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 5

Heh... I figured. If it helps, I almost did the same thing until I realized there was no correlation between the sub-queries.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)