T-SQL Triple Self Join

Question

Hey guys, ive have a problem with the following table:

CREATE TABLE [dbo].[CA](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Type] [char](1),
[Percent] [numeric](19, 5) NULL,
[Crop] [int] NULL,
[Currency] [int] NULL,
[Ammount] [decimal](19, 5) 
[ProductionSite] [int] NOT NULL
)

This table describes how a ProductionSite pays its suppliers. It may be with Crops, a money ammount or a Percentage of some other ammount not listed here.

The 'type' column is a discriminator which indicates what is the paying type. The thing is, you may pay with the three options at the same time, with two or with one. So, for a given ProductionSite you may have up to three rows in the table (this desing cannot be changed)

I need to create a view (SP/Triggers not allowed) that will show only one row per ProductionSite.

I not sure if im missing something, but im having a lot of trouble with this one :(

Almost forgot, this has an extra problem. The column order of the view must be always the same. Im saying this, as query plans are not always deterministic (if im correct) so a triple self association may give different results (in column/row order, not data)). Just to be clear,

SELECT * FROM A LEFT JOIN A AS B LEFT JOIN A AS C

may return a subset of the following:

columns(a) + columns(b) + columns(c)
columns(b) + columns(a) + columns(c)
etc..

any advice/answer will be very appreciated :)

Answer 1

Get a distinct list of the Production sites first, then left join the the table for the types.

Something like this

DECLARE @CA TABLE (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Type] [char](1),
    [Percent] [numeric](19, 5) NULL,
    [Crop] [int] NULL,
    [Currency] [int] NULL,
    [Ammount] [decimal](19, 5),
    [ProductionSite] [int] NOT NULL
    )


SELECT  *
FROm    (
      SELECT DISTINCT 
        [ProductionSite]
      FROM @CA
     ) DistinctSites LEFT JOIN
     @CA cType1 ON DistinctSites.ProductionSite = cType1.ProductionSite AND cType1.Type = 'Type1' LEFT JOIN
     @CA cType2 ON DistinctSites.ProductionSite = cType2.ProductionSite AND cType1.Type = 'Type2' LEFT JOIN
     @CA cType3 ON DistinctSites.ProductionSite = cType3.ProductionSite AND cType1.Type = 'Type3'

Answer 2

CREATE VIEW ThreePossiblePaymentDetailsAtOnce AS
SELECT COALESCE(T1.id, T2.id, T3.Id) AS SiteID,
       CASE 
         WHEN T1.id IS NOT NULL THEN T1.ProductionSite
         WHEN T2.id IS NOT NULL THEN T2.ProductionSite
         ELSE T3.ProductionSite
       END AS ProdSite,
       T1.Type, T1.Percent, T1.Crop,
       T2.Type, T2.Percent, T2.Currency,
       T3.Type, T3.Percent, T3,Ammount
FROM CA AS T1
FULL JOIN CA AS T2 ON T2.id = T1.id AND T2.Type = type_for_Currenty
FULL JOIN CA AS T3 ON T2.id = T1.id AND T2.Type = type_for_Ammount
WHERE T1.Type = type_for_crops
--AND here for additional conditions if desired
ORDER BY   -- or of course any other desired order, if any.
       CASE 
         WHEN T1.id IS NOT NULL THEN T1.ProductionSite
         WHEN T2.id IS NOT NULL THEN T2.ProductionSite
         ELSE T3.ProductionSite
       END

Explanation:
FULL JOIN provides a convenient way to bring in all rows (for a give Id + payment type) on the same result line. The difficulty is to be sure to show the Id (and I'm assuming the ProductionSite name as well), only once, regardless of where it comes from.
One way to do so (cf Astander's solution), is to first get a distinct list of Ids + ProductionSite value, and to use this to drive the query.
This solution is an alternative, using COALESCE which essentially finds the first non Null Id. We could probably get away with using COALESCE for the ProductionSite AS well, I figured I'd use the CASE on IDs' nullness instead, to be possibly a bit more semantically correct.

Answer 3

Try this

   Select c.ProductionSite, 
       Coalesce(c.type, m.type, p.type) type,
       c.Crop, m.Currency, p.Percent,
       Coalesce(c.type, m.type, p.type) type,
       Coalesce(c.Amount, m.Amount, p.Amount) Amount  
   From CA c -- for crops
     Full Join CA m -- for money
        On m.ProductionSite = c.ProductionSite
           And c.type = 'c'
           And m.type = 'm'
     Full Join CA p -- for percentage
        On p.ProductionSite = c.ProductionSite
           And c.type = 'c'
           And p.type = 'p'