CODEWITHSUNDEEP
sqlsql-servert-sql
TimHorton
TimHorton

Reputation: 885

Stored Procedure (T-SQL) Join multiple tables

I'm currently working on a t-sql query in Microsoft SQL Server Management Studio (SQL Server), which should gather data over several tables. In the end I'll need [OfferId] and [Label]. Do you have an idea on how to write those 4 query statements into 1?

SELECT a.OfferId AS [OfferId], a.OfferDataId AS [OfferDataId], b.DeliveryModelPoolId AS [DeliveryModelPoolId]
FROM [Offer].[Offer] a
INNER JOIN  [Offer].[OfferData] b
ON a.OfferDataId = b.OfferDataId

OfferId | OfferDataId | DeliveryModelPoolId
1..........| 1..................| 4

SELECT a.DeliveryModelPoolId AS [DeliveryModelPoolId], b.PoolId AS [PoolId]
FROM [Offer].[OfferData] a
INNER JOIN [Offer].[Pool] b
ON a.DeliveryModelPoolId = b.PoolId

DeliveryModelPoolId | PoolId
4................................| 4 

SELECT a.DeliveryModelId AS [DeliveryModelId]
FROM [Offer].[Delivery] a
INNER JOIN [Offer].[Pool] b
ON a.DeliveryModelPoolId = b.PoolId

DeliveryModelId
2
6

SELECT a.Label AS [Label]
FROM [Offer].[DeliveryModel] a
INNER JOIN [Offer].[DeliveryLabels] b
ON a.DeliveryModelId = b.DeliveryModelId

Label
Service Center
Delivery By Car

Thanks a lot! :)

Upvotes: 0

Views: 1419

Answers (2)

Henrico
Henrico

Reputation: 21

If you are planning to reuse the query, I would put it into a view:

CREATE VIEW vWOfferData
AS   
SELECT a.offerID, dl.label
FROM [Offer].[Offer] a
INNER JOIN  [Offer].[OfferData] b
ON a.OfferDataId = b.OfferDataId
INNER JOIN [Offer].[Pool] p2
ON b.DeliveryModelPoolId = p2.PoolId
INNER JOIN [Offer].[Delivery] d3
ON d3.DeliveryModelPoolId = p2.PoolId
INNER JOIN [Offer].[DeliveryModel] dl
ON dl.DeliveryModelId = d3.DeliveryModelId

You can then use it as a table. For example:

SELECT * FROM vWOfferData

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

You can join more than once in a query

select a.offerID, dl.label
FROM [Offer].[Offer] a
INNER JOIN  [Offer].[OfferData] b
ON a.OfferDataId = b.OfferDataId
inner join [Offer].[Pool] p2
ON b.DeliveryModelPoolId = p2.PoolId
inner join [Offer].[Delivery] d3
ON d3.DeliveryModelPoolId = p2.PoolId
inner join [Offer].[DeliveryModel] dl
  on dl.DeliveryModelId = d3.DeliveryModelId

Upvotes: 0

Related Questions