Collapsing overlapping rows from multiple joined tables into as few rows as possible

Question

Table_Design:

ID
"Alpha"
"Beta"

Table_Size:

Design_ID   Size
"Alpha"     S
"Alpha"     M
"Alpha"     L
"Beta"      S
"Beta"      L

Table_Color:

Design_Id   Color
"Alpha"     "Black"
"Alpha"     "Blue"
"Alpha"     "Red"
"Alpha"     "Green"
"Beta"      "Orange"

select D.ID, S.Size, C.Color from
Table_Design D
Left Outer Join
Table_Size S
ON D.ID = S.Design_Id
Left Outer Join
Table_Color C
ON D.ID = C.Design_Id
where
D.ID = 'Alpha'

Yields:

D.ID    S.Size  C.Color
Alpha   S       Black
Alpha   S       Blue
Alpha   S       Red
Alpha   S       Green
Alpha   M       Black
Alpha   M       Blue
Alpha   M       Red
Alpha   M       Green
Alpha   L       Black
Alpha   L       Blue
Alpha   L       Red
Alpha   L       Green

Is there anyway to write a query to return this instead:

D.ID    S.Size  C.Color
Alpha   S       Black
Alpha   M       Blue
Alpha   L       Red
Alpha           Green

Answer 1

I don't really favor combining data in this way, but you could do something like the following:

Select Design_Id, 'Color' as Type, Color as Value from Table_Color where Design_Id = 'Alpha'
union all
Select Design_Id, 'Size' as Type, Size as Value from Table_Size where Design_Id = 'Alpha'

which gives

Design_Id Type  Value
========= ====  =====
Alpha     Color Black
Alpha     Color Blue
Alpha     Color Red
Alpha     Color Green
Alpha     Size  S
Alpha     Size  M
Alpha     Size  L

Answer 2

I assume that you want to combine the n-th line with sizes with the n-th line with colors. With the table design you are showing it will not be possible because you can't reliably identify the n-th line the tables.

I'd say redesign: make a separate table to link a design with both a size and a color (which can be NULL) and your queries will be a lot easier.