Skip to content

Instantly share code, notes, and snippets.

@marto83
Created December 13, 2012 17:09
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save marto83/4278014 to your computer and use it in GitHub Desktop.
Save marto83/4278014 to your computer and use it in GitHub Desktop.
Sql relating to SO question http://stackoverflow.com/q/13864675/29555
SELECT [Project2].[Id2] AS [Id],
[Project2].[Id3] AS [Id1],
[Project2].[Id6] AS [Id2],
[Project2].[Id4] AS [Id3],
[Project2].[Id8] AS [Id4],
[Project2].[Id7] AS [Id5],
[Project2].[Id11] AS [Id6],
[Project2].[Id12] AS [Id7],
[Project2].[Id15] AS [Id8],
[Project2].[Id13] AS [Id9],
[Project2].[Id17] AS [Id10],
[Project2].[Id16] AS [Id11],
[Project2].[Id] AS [Id12],
[Project2].[OrderId] AS [OrderId],
[Project2].[Name] AS [Name],
[Project2].[Email] AS [Email],
[Project2].[ChargeType] AS [ChargeType],
[Project2].[Status] AS [Status],
[Project2].[Created] AS [Created],
[Project2].[Updated] AS [Updated],
[Project2].[Id1] AS [Id13],
[Project2].[Addressee] AS [Addressee],
[Project2].[AddressLine1] AS [AddressLine1],
[Project2].[AddressLine2] AS [AddressLine2],
[Project2].[AddressLine3] AS [AddressLine3],
[Project2].[Town] AS [Town],
[Project2].[County] AS [County],
[Project2].[Country] AS [Country],
[Project2].[Postcode] AS [Postcode],
[Project2].[Id5] AS [Id14],
[Project2].[Id9] AS [Id15],
[Project2].[Id10] AS [Id16],
[Project2].[Addressee1] AS [Addressee1],
[Project2].[AddressLine11] AS [AddressLine11],
[Project2].[AddressLine21] AS [AddressLine21],
[Project2].[AddressLine31] AS [AddressLine31],
[Project2].[Town1] AS [Town1],
[Project2].[County1] AS [County1],
[Project2].[Country1] AS [Country1],
[Project2].[Postcode1] AS [Postcode1],
[Project2].[Id14] AS [Id17],
[Project2].[Id18] AS [Id18],
[Project2].[C1] AS [C1],
[Project2].[Id19] AS [Id19],
[Project2].[Description] AS [Description],
[Project2].[UnitPrice] AS [UnitPrice],
[Project2].[Quantity] AS [Quantity],
[Project2].[SKU] AS [SKU],
[Project2].[ShippingCost] AS [ShippingCost],
[Project2].[Tax] AS [Tax],
[Project2].[Order_Id] AS [Order_Id]
FROM (SELECT [Limit1].[Id] AS [Id],
[Limit1].[OrderId] AS [OrderId],
[Limit1].[Name] AS [Name],
[Limit1].[Email] AS [Email],
[Limit1].[ChargeType] AS [ChargeType],
[Limit1].[Status] AS [Status],
[Limit1].[Created] AS [Created],
[Limit1].[Updated] AS [Updated],
[Limit1].[Id1] AS [Id1],
[Limit1].[Addressee] AS [Addressee],
[Limit1].[AddressLine1] AS [AddressLine1],
[Limit1].[AddressLine2] AS [AddressLine2],
[Limit1].[AddressLine3] AS [AddressLine3],
[Limit1].[Town] AS [Town],
[Limit1].[County] AS [County],
[Limit1].[Country] AS [Country],
[Limit1].[Postcode] AS [Postcode],
[Limit1].[Id2] AS [Id2],
[Limit1].[Id3] AS [Id3],
[Limit1].[Id4] AS [Id4],
[Limit1].[Id5] AS [Id5],
[Limit1].[Id6] AS [Id6],
[Limit1].[Id7] AS [Id7],
[Limit1].[Id8] AS [Id8],
[Limit1].[Id9] AS [Id9],
[Limit1].[Id10] AS [Id10],
[Limit1].[Addressee1] AS [Addressee1],
[Limit1].[AddressLine11] AS [AddressLine11],
[Limit1].[AddressLine21] AS [AddressLine21],
[Limit1].[AddressLine31] AS [AddressLine31],
[Limit1].[Town1] AS [Town1],
[Limit1].[County1] AS [County1],
[Limit1].[Country1] AS [Country1],
[Limit1].[Postcode1] AS [Postcode1],
[Limit1].[Id11] AS [Id11],
[Limit1].[Id12] AS [Id12],
[Limit1].[Id13] AS [Id13],
[Limit1].[Id14] AS [Id14],
[Limit1].[Id15] AS [Id15],
[Limit1].[Id16] AS [Id16],
[Limit1].[Id17] AS [Id17],
[Limit1].[Id18] AS [Id18],
[Extent20].[Id] AS [Id19],
[Extent20].[Description] AS [Description],
[Extent20].[UnitPrice] AS [UnitPrice],
[Extent20].[Quantity] AS [Quantity],
[Extent20].[SKU] AS [SKU],
[Extent20].[ShippingCost] AS [ShippingCost],
[Extent20].[Tax] AS [Tax],
[Extent20].[Order_Id] AS [Order_Id],
CASE
WHEN ([Extent20].[Id] IS NULL) THEN CAST(NULL AS int)
ELSE 1
END AS [C1]
FROM (SELECT TOP (1) [Extent1].[Id] AS [Id],
[Extent1].[OrderId] AS [OrderId],
[Extent1].[Name] AS [Name],
[Extent1].[Email] AS [Email],
[Extent1].[ChargeType] AS [ChargeType],
[Extent1].[Status] AS [Status],
[Extent1].[Created] AS [Created],
[Extent1].[Updated] AS [Updated],
[Join2].[Id1] AS [Id1],
[Join2].[Addressee] AS [Addressee],
[Join2].[AddressLine1] AS [AddressLine1],
[Join2].[AddressLine2] AS [AddressLine2],
[Join2].[AddressLine3] AS [AddressLine3],
[Join2].[Town] AS [Town],
[Join2].[County] AS [County],
[Join2].[Country] AS [Country],
[Join2].[Postcode] AS [Postcode],
[Join2].[Id2] AS [Id2],
[Join2].[Id3] AS [Id3],
[Join5].[Id4] AS [Id4],
[Join5].[Id5] AS [Id5],
[Join5].[Id6] AS [Id6],
[Join8].[Id7] AS [Id7],
[Join8].[Id8] AS [Id8],
[Join8].[Id9] AS [Id9],
[Join11].[Id10] AS [Id10],
[Join11].[Addressee] AS [Addressee1],
[Join11].[AddressLine1] AS [AddressLine11],
[Join11].[AddressLine2] AS [AddressLine21],
[Join11].[AddressLine3] AS [AddressLine31],
[Join11].[Town] AS [Town1],
[Join11].[County] AS [County1],
[Join11].[Country] AS [Country1],
[Join11].[Postcode] AS [Postcode1],
[Join11].[Id11] AS [Id11],
[Join11].[Id12] AS [Id12],
[Join14].[Id13] AS [Id13],
[Join14].[Id14] AS [Id14],
[Join14].[Id15] AS [Id15],
[Join17].[Id16] AS [Id16],
[Join17].[Id17] AS [Id17],
[Join17].[Id18] AS [Id18]
FROM [dbo].[Orders] AS [Extent1]
LEFT OUTER JOIN (SELECT [Extent2].[Id] AS [Id1],
[Extent2].[Addressee] AS [Addressee],
[Extent2].[AddressLine1] AS [AddressLine1],
[Extent2].[AddressLine2] AS [AddressLine2],
[Extent2].[AddressLine3] AS [AddressLine3],
[Extent2].[Town] AS [Town],
[Extent2].[County] AS [County],
[Extent2].[Country] AS [Country],
[Extent2].[Postcode] AS [Postcode],
[Extent3].[Id] AS [Id2],
[Extent4].[Id] AS [Id3]
FROM [dbo].[Addresses] AS [Extent2]
LEFT OUTER JOIN [dbo].[Orders] AS [Extent3]
ON ([Extent3].[BillingAddress_Id] IS NOT NULL)
AND ([Extent2].[Id] = [Extent3].[BillingAddress_Id])
LEFT OUTER JOIN [dbo].[Orders] AS [Extent4]
ON ([Extent4].[DeliveryAddress_Id] IS NOT NULL)
AND ([Extent2].[Id] = [Extent4].[DeliveryAddress_Id])) AS [Join2]
ON [Extent1].[BillingAddress_Id] = [Join2].[Id1]
LEFT OUTER JOIN (SELECT [Extent5].[Id] AS [Id4],
[Extent6].[Id] AS [Id5],
[Extent7].[Id] AS [Id6]
FROM [dbo].[Addresses] AS [Extent5]
LEFT OUTER JOIN [dbo].[Orders] AS [Extent6]
ON ([Extent6].[BillingAddress_Id] IS NOT NULL)
AND ([Extent5].[Id] = [Extent6].[BillingAddress_Id])
LEFT OUTER JOIN [dbo].[Orders] AS [Extent7]
ON ([Extent7].[DeliveryAddress_Id] IS NOT NULL)
AND ([Extent5].[Id] = [Extent7].[DeliveryAddress_Id])) AS [Join5]
ON [Extent1].[BillingAddress_Id] = [Join5].[Id4]
LEFT OUTER JOIN (SELECT [Extent8].[Id] AS [Id7],
[Extent9].[Id] AS [Id8],
[Extent10].[Id] AS [Id9]
FROM [dbo].[Addresses] AS [Extent8]
LEFT OUTER JOIN [dbo].[Orders] AS [Extent9]
ON ([Extent9].[BillingAddress_Id] IS NOT NULL)
AND ([Extent8].[Id] = [Extent9].[BillingAddress_Id])
LEFT OUTER JOIN [dbo].[Orders] AS [Extent10]
ON ([Extent10].[DeliveryAddress_Id] IS NOT NULL)
AND ([Extent8].[Id] = [Extent10].[DeliveryAddress_Id])) AS [Join8]
ON [Extent1].[BillingAddress_Id] = [Join8].[Id7]
LEFT OUTER JOIN (SELECT [Extent11].[Id] AS [Id10],
[Extent11].[Addressee] AS [Addressee],
[Extent11].[AddressLine1] AS [AddressLine1],
[Extent11].[AddressLine2] AS [AddressLine2],
[Extent11].[AddressLine3] AS [AddressLine3],
[Extent11].[Town] AS [Town],
[Extent11].[County] AS [County],
[Extent11].[Country] AS [Country],
[Extent11].[Postcode] AS [Postcode],
[Extent12].[Id] AS [Id11],
[Extent13].[Id] AS [Id12]
FROM [dbo].[Addresses] AS [Extent11]
LEFT OUTER JOIN [dbo].[Orders] AS [Extent12]
ON ([Extent12].[BillingAddress_Id] IS NOT NULL)
AND ([Extent11].[Id] = [Extent12].[BillingAddress_Id])
LEFT OUTER JOIN [dbo].[Orders] AS [Extent13]
ON ([Extent13].[DeliveryAddress_Id] IS NOT NULL)
AND ([Extent11].[Id] = [Extent13].[DeliveryAddress_Id])) AS [Join11]
ON [Extent1].[DeliveryAddress_Id] = [Join11].[Id10]
LEFT OUTER JOIN (SELECT [Extent14].[Id] AS [Id13],
[Extent15].[Id] AS [Id14],
[Extent16].[Id] AS [Id15]
FROM [dbo].[Addresses] AS [Extent14]
LEFT OUTER JOIN [dbo].[Orders] AS [Extent15]
ON ([Extent15].[BillingAddress_Id] IS NOT NULL)
AND ([Extent14].[Id] = [Extent15].[BillingAddress_Id])
LEFT OUTER JOIN [dbo].[Orders] AS [Extent16]
ON ([Extent16].[DeliveryAddress_Id] IS NOT NULL)
AND ([Extent14].[Id] = [Extent16].[DeliveryAddress_Id])) AS [Join14]
ON [Extent1].[DeliveryAddress_Id] = [Join14].[Id13]
LEFT OUTER JOIN (SELECT [Extent17].[Id] AS [Id16],
[Extent18].[Id] AS [Id17],
[Extent19].[Id] AS [Id18]
FROM [dbo].[Addresses] AS [Extent17]
LEFT OUTER JOIN [dbo].[Orders] AS [Extent18]
ON ([Extent18].[BillingAddress_Id] IS NOT NULL)
AND ([Extent17].[Id] = [Extent18].[BillingAddress_Id])
LEFT OUTER JOIN [dbo].[Orders] AS [Extent19]
ON ([Extent19].[DeliveryAddress_Id] IS NOT NULL)
AND ([Extent17].[Id] = [Extent19].[DeliveryAddress_Id])) AS [Join17]
ON [Extent1].[DeliveryAddress_Id] = [Join17].[Id16]
WHERE [Extent1].[OrderId] = 'Test-9925c1f1' /* @p__linq__0 */) AS [Limit1]
LEFT OUTER JOIN [dbo].[OrderLines] AS [Extent20]
ON [Limit1].[Id] = [Extent20].[Order_Id]) AS [Project2]
ORDER BY [Project2].[Id2] ASC,
[Project2].[Id3] ASC,
[Project2].[Id6] ASC,
[Project2].[Id4] ASC,
[Project2].[Id8] ASC,
[Project2].[Id7] ASC,
[Project2].[Id11] ASC,
[Project2].[Id12] ASC,
[Project2].[Id15] ASC,
[Project2].[Id13] ASC,
[Project2].[Id17] ASC,
[Project2].[Id16] ASC,
[Project2].[Id] ASC,
[Project2].[Id1] ASC,
[Project2].[Id5] ASC,
[Project2].[Id9] ASC,
[Project2].[Id10] ASC,
[Project2].[Id14] ASC,
[Project2].[Id18] ASC,
[Project2].[C1] ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment