Fetching record from one sql query and using it in another

Question

I have a question regarding SQL.

SELECT TOP (10) ordr.ordID
FROM [inSight].[dbo].[Orders] ordr
INNER JOIN [inSight].[dbo].[OrderLines] ordn ON ordr.ordID = ordn.ordID
JOIN [inSight].[dbo].[OrderLineAttributeValues] atr ON ordn.olnID = atr.olnID
WHERE
    atr.atbID=190 AND
    atr.olnavValue=1

(the above query returns more than one result)

So what I want to do is, grab the result from the above query and use it in the following query one by one.

SELECT op.optCode
FROM [inSight].[dbo].[Orders] ordr
INNER JOIN [inSight].[dbo].[OrderLines] ordn ON ordr.ordID = ordn.ordID
JOIN [inSight].[dbo].[OrderLineOptions] ordlo ON ordn.olnID = ordlo.olnID
JOIN [inSight].[dbo].[Features] ftr ON ordlo.ftrID = ftr.ftrID
JOIN [inSight].[dbo].[Options] op ON ftr.ftrID = op.ftrID
WHERE
    ordr.ordID = @OrderNumber AND
    ftr.ftrID = 1477 AND
    ordlo.optid = op.optID

Variable OrderNumber above is the result from the first query.

I don't want to use cursor, so is there any other way to solve this issue?

I need to fetch the record from the first query one by one! Adding a where clause won't help much! — CodeJockey
– CodeJockey, Commented Dec 12, 2012 at 20:50
What do you mean by "one by one"? There's nothing in your query above that needs that. And valex's correct response wouldn't add a WHERE clause, it would just alter the one that's already there. — RBarryYoung
– RBarryYoung, Commented Dec 12, 2012 at 20:54
Yes it did work, Sorry valex.. your solution was right, apparently i was misinturpreting it. Thanks RBarryYoung :) Much appreciated! — CodeJockey
– CodeJockey, Commented Dec 13, 2012 at 21:32

animateme · Accepted Answer · 2012-12-12 21:19:06Z

1

Have you looked at cross apply?

This may work for you:

SELECT op.optCode
FROM [inSight].[dbo].[Orders] ordr
INNER JOIN [inSight].[dbo].[OrderLines] ordn ON ordr.ordID = ordn.ordID
JOIN [inSight].[dbo].[OrderLineOptions] ordlo ON ordn.olnID = ordlo.olnID
JOIN [inSight].[dbo].[Features] ftr ON ordlo.ftrID = ftr.ftrID
JOIN [inSight].[dbo].[Options] op ON ftr.ftrID = op.ftrID
CROSS APPLY (
SELECT TOP (10) ordr.ordID
FROM [inSight].[dbo].[Orders] ordr
INNER JOIN [inSight].[dbo].[OrderLines] ordn ON ordr.ordID = ordn.ordID
JOIN [inSight].[dbo].[OrderLineAttributeValues] atr ON ordn.olnID = atr.olnID
WHERE
    atr.atbID=190 AND
    atr.olnavValue=1 AND
    ordr.ordID = op.optCode
    ) ca
WHERE
    ftr.ftrID = 1477 AND
    ordlo.optid = op.optID

answered Dec 12, 2012 at 21:19

animateme

3091 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fetching record from one sql query and using it in another

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related