Selecting all combinations and show which don't exist

I have three tables: X, Y and XY. Each table has a field called [Identity] which is the primary key. (And an auto-increment field开发者_高级运维.) Table XY has two other fields, one to link to X and the other to link to Y. (Named [X_Identity] and [Y_Identity])The combination of these links is an unique key. Table X has 6 fields (X1 to X6) and table Y has 3 fields (Y1 to Y3). Table XY has 4 fields called X2, X4, X5 and Y2. These are similar in purpose as the fields in the X or Y tables with the same name. Table X will be about 200 records, Y about 10 records. Table XY has about 75 records, out of a possible 2000 records.

Now my problem:

I need a view that generates all 2000 combinations of X and Y and it should return values from the XY record if a record for such a combination exists! If not, it should just combine the fields from X and Y. Thus this table has two possible record types:

• [X].[X1], [X].[X2], [X].[X3], [X].[X4], [X].[X5], [X].[X5], [Y].Y1], [Y].Y2], [Y].Y3] if no XY record is found.
• [X].[X1], [XY].[X2], [X].[X3], [XY].[X4], [XY].[X5], [X].[X5], [Y].Y1], [XY].Y2], [Y].Y3] if an existing XY record is found.

Seems to me I have to do a union between a selection of all XY records to generate one half of the result. (Easy!) And I need to somehow combine the X and Y table for which there's no XY record to generate the other half. And that last one is a bit complex... Suggestions?

Oh, and it's going to be a read-only view in my system! So it needs to be a single SQL statement!

I was thinking along the lines of creating a cross join of X and Y, and then replace in the result set the values that are available in XY:

SELECT [X].[X1] AS [X1]
       ,COALESCE([XY].[X2], [X].[X2]) AS [X2]
       ,[X].[X3] AS [X3]
       ,COALESCE([XY].[X4], [X].[X4]) AS [X4]
       ,COALESCE([XY].[X5], [X].[X5]) AS [X5]
       ,[X].[X6] AS [X6]
       ,[Y].[Y1] AS [Y1]
       ,COALESCE([XY].[Y2], [Y].[Y2]) AS [Y2]
       ,[Y].[Y3] AS [Y3]
FROM [X]
CROSS JOIN [Y]
LEFT OUTER JOIN [XY]
ON [XY].[X_Identity] = [X].[Identity]
AND [XY].[Y_Identity] = [Y].[Identity]

Have not tried this, so it might need some tweaking.

Here's my answer.

SELECT [X].[X1] AS [X1]
       ,CASE WHEN XY.X_Identity is not null then [XY].[X2] else [X].[X2] end [X2]
       ,[X].[X3] AS [X3]
       ,CASE WHEN XY.X_Identity is not null then [XY].[X4] else [X].[X4] end [X4]
       ,CASE WHEN XY.X_Identity is not null then [XY].[X5] else [X].[X5] end [X5]
       ,[X].[X6] AS [X6]
       ,[Y].[Y1] AS [Y1]
       ,CASE WHEN XY.X_Identity is not null then [XY].[Y2] else [Y].[Y2] end [Y2]
       ,[Y].[Y3] AS [Y3]
FROM (X
CROSS JOIN Y)
LEFT OUTER JOIN XY
ON [XY].[X_Identity] = [X].[Identity]
AND [XY].[Y_Identity] = [Y].[Identity]

This is based on the direct application of "CASE (pk) == does it exist" using one of the answers. However, you might want to look at the other form of writing LEFT JOIN:

SELECT  [X].[X1]
       ,[XY].[X2]
       ,[X].[X3]
       ,[XY].[X4]
       ,[XY].[X5]
       ,[X].[X6]
       ,[Y].[Y1]
       ,[XY].[Y2]
       ,[Y].[Y3]
FROM (X
CROSS JOIN Y)
INNER JOIN XY ON [XY].[X_Identity] = [X].[Identity]
             AND [XY].[Y_Identity] = [Y].[Identity]

UNION ALL

SELECT  [X].[X1]
       ,[X].[X2]
       ,[X].[X3]
       ,[X].[X4]
       ,[X].[X5]
       ,[X].[X6]
       ,[Y].[Y1]
       ,[Y].[Y2]
       ,[Y].[Y3]
FROM (X
CROSS JOIN Y)
WHERE NOT EXISTS (
    SELECT * FROM XY
    WHERE [XY].[X_Identity] = [X].[Identity]
      AND [XY].[Y_Identity] = [Y].[Identity])

I managed to find a solution myself, although not too pretty:

SELECT 
    [X].[X1] AS [X1],
    [XY].[X2] AS [X2],
    [X].[X3] AS [X3],
    [XY].[X4] AS [X4],
    [XY].[X5] AS [X5],
    [X].[X6] AS [X6],
    [Y].[Y1] AS [Y1],
    [XY].[Y2] AS [Y2],
    [Y].[Y3] AS [Y3]
FROM 
    [X], 
    [Y],
    [XY]
WHERE
    [XY].[X_Identity] = [X].[Identity]
AND
    [XY].[Y_Identity] = [Y].[Identity]

UNION

SELECT 
    [X].[X1] AS [X1],
    [X].[X2] AS [X2],
    [X].[X3] AS [X3],
    [X].[X4] AS [X4],
    [X].[X5] AS [X5],
    [X].[X6] AS [X6],
    [Y].[Y1] AS [Y1],
    [Y].[Y2] AS [Y2],
    [Y].[Y3] AS [Y3]
FROM 
    [X], 
    [Y]
WHERE
    NOT (CAST([X].[Identity] AS nvarchar(12)) + '/' + CAST([Y].[Identity] AS nvarchar(12))) IN (
    SELECT
        CAST([XY].[X_Identity] AS nvarchar(12)) + '/' + CAST([XY].[Y_Identity] AS nvarchar(12))
    FROM 
        [XY]
    ))

It's a union of two select statements (and even a third), where the first one selects all records from XY and adds the related data from the X and Y tables to it.
The second select is doing some additional calculation to combine the X and Y table into one field which I can look up in the XY table by doing the same calculation there.

While it works, I just wonder if this can be done easier...

Josien came close and gave me an idea for a second solution to do this! It doesn't require me to do the calculations and thus it's slightly more reliable. But it's still not very pretty.

SELECT  
    [COMBI].[X1] AS [X1],
    COALESCE([XY].[X2], [COMBI].[X2]) AS [X2],
    [COMBI].[X3] AS [X3],
    COALESCE([XY].[X4], [COMBI].[X4]) AS [X4],
    COALESCE([XY].[X5], [COMBI].[X5]) AS [X5],
    [COMBI].[X6] AS [X6],
    [COMBI].[Y1] AS [Y1],
    COALESCE([XY].[Y2], [COMBI].[Y2]) AS [Y2],
    [COMBI].[Y3] AS [Y3]
FROM (
    SELECT 
        [X].[Identity] AS [X_Identity],
        [Y].[Identity] AS [Y_Identity],
        [X].[X1] AS [X1],
        [X].[X2] AS [X2],
        [X].[X3] AS [X3],
        [X].[X4] AS [X4],
        [X],[X5] AS [X5],
        [X].[X6] AS [X6],
        [Y].[Y1] AS [Y1],
        [Y].[Y2] AS [Y2],
        [Y].[Y3] AS [Y3]
    FROM 
        [X]
    CROSS JOIN 
        [Y]
} AS [COMBI]
LEFT OUTER JOIN 
    [XY]
ON 
    [XY].[X_Identity] = [COMBI].[X_Identity]
AND 
    [XY].[Y_Identity] = [COMBI].[Y_Identity]

I actually forgot about COALESCE. :-)

Only thing is that it has a small problem! While XY can have a value, one of it's fields can still be NULL. If that's the case, it won't override the value from X or Y with the NULL value from XY but keep the old value.
It's why it's a good solution, but I need to override values from X and Y with all values from XY, even if those are NULL.