Sub query driving me crazy - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Sub query driving me crazy

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-12-04 : 06:42:05

Hi,

I'm trying to write the following into a single query.

@figuresId int

(query1)
SELECT vehicleId FROM tblFigures WHERE figuresId = @figuresId

(query2)
SELECT capId FROM tbl1 WHERE vehicleId = (result from q1)
UNION
SELECT capId FROM tbl2 WHERE vehicleId = (result from q1)
UNION
SELECT capId FROM tbl3 WHERE vehicleId = (result from q1)
UNION

I want to return CapId

Thanks

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-04 : 06:59:06

SELECT capId FROM tbl1 WHERE vehicleId IN (SELECT vehicleId FROM tblFigures WHERE figuresId = @figuresId)
UNION
SELECT capId FROM tbl2 WHERE vehicleId IN (SELECT vehicleId FROM tblFigures WHERE figuresId = @figuresId)
UNION
SELECT capId FROM tbl3 WHERE vehicleId IN (SELECT vehicleId FROM tblFigures WHERE figuresId = @figuresId)

Replace "=" with IN and try..
Check whether you are getting the required output or not..

Balaji.K

creieru
Starting Member

12 Posts

Posted - 2009-12-04 : 07:06:20

or

select X.capID
from tblFigures as F
inner join
( select capID, vehicleID from tbl1
union
select capID, vehicleID from tbl2
union
select capID, vehicleID from tbl3
) as X on F.vehicleID = X.vehicleID
where F.figuresId = @figuresId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources