CODEWITHSUNDEEP
c#sql-serversql-server-2008
revolutionkpi
revolutionkpi

Reputation: 2682

How to use the results of the previous query in the next query?

As a result of this query I have a table:

select i.id, o.[name]  from Item i
LEFT OUTER JOIN sys.objects o on o.[name]='I' + cast(i.id as nvarchar(20))
where o.name is not null

enter image description here

Now I need to use the result of this table in the next query:

select PriceListItem.ProductExternalId, @id.Id, @id.FriendlyName, @id.BriefWiki,
    [PriceListItem].[ProductExternalDesc]
from [@id]
inner join [Product]  on Product.ItemId = @name and Product.InstanceId = @id.ID
inner join [PriceListItem]  on Product.ID = PriceListItem.ProductId

instead of '@id' I should use data from the table with name= id, and instead of '@name' I should use data from the table with name= name

Upvotes: 0

Views: 124

Answers (3)

Yuck
Yuck

Reputation: 50835

Since you're on SQL 2K8 you can use a CTE:

-- You may need a ; before WITH as in ;WITH
WITH FirstQuery AS (
  select i.id, o.[name]  from Item i
  LEFT OUTER JOIN sys.objects o on o.[name]='I' + cast(i.id as nvarchar(20))
  where o.name is not null
)
select PriceListItem.ProductExternalId,
       FQ.Id,
       -- Neither of these are in your FirstQuery so you can not use them
       -- @id.FriendlyName, @id.BriefWiki,
       [PriceListItem].[ProductExternalDesc]
from FirstQuery FQ
     inner join [Product] on Product.ItemId = FQ.name
                         and Product.InstanceId = FQ.ID
     inner join [PriceListItem]  on Product.ID = PriceListItem.ProductId;

From the queries alone it's tough to tell how you plan to JOIN them, but this will allow you to make use of the first query in the subsequent one.

Looks like you have some syntax errors in your second query - @id.id?

Upvotes: 1

gbn
gbn

Reputation: 432230

Standard SQL way, works in most RDBMS

select PriceListItem.ProductExternalId, @id.Id, @id.FriendlyName, @id.BriefWiki,
    [PriceListItem].[ProductExternalDesc]
from 
    (
    select i.id, o.[name]  from Item i
    LEFT OUTER JOIN sys.objects o on o.[name]='I' + cast(i.id as nvarchar(20))
    where o.name is not null
    )
    X
    inner JOIN
    [@id] ON X.id = @id.id --change here as needed
    inner join [Product]  on Product.ItemId = @name and Product.InstanceId = @id.ID
    inner join [PriceListItem]  on Product.ID = PriceListItem.ProductId*

Upvotes: 1

Elastep
Elastep

Reputation: 3408

select i.id, o.[name]  from Item i
into @temp_table
LEFT OUTER JOIN sys.objects o on o.[name]='I' + cast(i.id as nvarchar(20))
where o.name is not null

Now you can use @temp_table as you want :)

Upvotes: 0

Related Questions