SQL Join Problem

Question

Table one contains

ID|Name  
1  Mary  
2  John  

Table two contains

ID|Color  
1  Red  
2  Blue  
2  Green  
2  Black  

I want to end up with is

ID|Name|Red|Blue|Green|Black  
1  Mary Y   Y  
2  John     Y     Y     Y

Thanks for any help.

Answer 1

If you use T-SQL you can use PIVOT (http://msdn.microsoft.com/en-us/library/ms177410.aspx)

Here is query I used:

declare @tbl_names table(id int, name varchar(100))
declare @tbl_colors table(id int, color varchar(100))

insert into @tbl_names
select 1, 'Mary'
union
select 2, 'John'


insert into @tbl_colors
select 1, 'Red'
union
select 1, 'Blue'
union
select 2, 'Green'
union
select 2, 'Blue'
union
select 2, 'Black'

select name,
        case when [Red] is not null then 'Y' else '' end as Red,
        case when [Blue] is not null then 'Y' else '' end as Blue,
        case when [Green] is not null then 'Y' else '' end as Green,
        case when [Black] is not null then 'Y' else '' end as Black

from
(
select n.id, name, color from @tbl_names n
inner join @tbl_colors c on n.id = c.id
) as subq
pivot 
(
    min(id)
    FOR color IN ([Red], [Blue], [Green], [Black])
) as pvt

And here is output:

John        Y   Y   Y
Mary    Y   Y       

Answer 2

I think you're going to have to end up with something like this :

SELECT  t1.ID, 
        t1.Name, 
        CASE 
            WHEN red.ID IS NULL THEN '' 
            ELSE 'Y' 
        END As Red,
        CASE 
            WHEN blue.ID IS NULL THEN '' 
            ELSE 'Y' 
        END As Blue
FROM    Table1 t1 
    LEFT JOIN   Table2 Red 
        ON t1.ID = Red.ID AND Red.Color = 'Red'
    LEFT JOIN   Table2 Blue
        ON t1.ID = Blue.ID AND Blue.Color = 'Blue'

MS Sql does not support PIVOT queries like MS Access.

Answer 3

Contrary to what some other posters have said; I see no need for a third table. If colors are a well known enumeration in you application then you don't need a "Color" table.

What you are looking for is a PIVOT like this one.

Answer 4

I can use a CASE statement with a subquery to input the Y values.

select ID, Name,
  case
    when exists (select * from Colors C where C.ID = N.ID and Color = 'Red') then
      'Y'
    else
      NULL
  end
,
 case
    when exists (select * from Colors C where C.ID = N.ID and Color = 'Blue') then
      'Y'
    else
      NULL
  end
,
 case
    when exists (select * from Colors C where C.ID = N.ID and Color = 'Green') then
      'Y'
    else
      NULL
  end
,
 case
    when exists (select * from Colors C where C.ID = N.ID and Color = 'Black') then
      'Y'
    else
      NULL
  end
from Names N

Answer 5

As other commenters have pointed out, you don't display exactly how you are linking people and colors. If you are using a linking table (person_id, color_id) then there is no way to solve this problem in standard SQL since it requires a pivot or cross-tabulation, which is not part of standard SQL.

If you are willing to add the condition that the number of colors is limited and known and design time, you could come up with a solution using one join for each color and CASE or IF functions in the SQL. But that would not be elegant and, furthermore, I wouldn't trust that condition to stay true for very long.

If you are able to come up with a different way of storing the color linking information you might have more options for producing the output you want, but a different storage technique implies some degree of denormalization of the database which could well cause other difficulties.

Otherwise, you will have to do this in a stored procedure or application code.

Answer 6

Thanks for the responses. I'm going to re-post this with some additional info about exactly what I'm trying to do that may complicate this. Can someone close this?