CODEWITHSUNDEEP
sql-server-2008
Jabsy
Jabsy

Reputation: 171

In a stored procedure, how can I use the results of one query to be used in another

I'm writing a stored procedure that will bring back a lot of project information. Due to the structure of the existing DB it's been decided that this is the approach we need to take.

My base query is going to be a

Select * from Projects where ProjectID = @projectID

This will return something like

ProjectID || Name || Component1Type || Component2Type

After getting those results, I want to then query the Component1 and component2 tables and return the names of those components with something along the lines of

Select Component1.Name from Component1 where ComponentID = Component1Type (where this is the result of the previous query)

Which will return just the Name

After doing this for 3 or four more items, I want to append them back to the original query so I get one row back which is

ProjectID || Name || Component1Type || Component2Type || Name1 || Name2 || etc...

Any idea on how to do this? Do I want to use temp tables or is there a simpler way to go about accomplishing this?

Upvotes: 0

Views: 215

Answers (1)

amit_g
amit_g

Reputation: 31270

Select P.*, C1.Name as Name1, C2.Name as Name2 
from Projects P
left join Component1 C1 
  on P.Component1Type = C1.ComponentID
left join Component2 C2 
  on P.Component2Type = C2.ComponentID
where P.ProjectID = @projectID

Upvotes: 3

Related Questions