SQL Join Tables

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.

---

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?

Lachance

-1: You need to tell us which DBMS you're using. – Ebullience

I'm using ADO to connect to a Visual Foxpro DB. – Lachance

I think I'm missing something... As pointed out by another, There is no relationship between table 1 and 2. Table 2 looks like just colors with their own ID, and not the ID of the person. There has to be something that associates the tables, unless your data for table two should be showing 1=red, 1=blue, 2=blue, 2=green, 2=black. If so, a query could be designed. – Renaerenaissance

Yes, there is a big discrepancy on the ID values versus what your result set shows. There is either a linking tbale you aren't showing us, or the example values in your Colors table are not correct(there should be 5 rows with three ID=2 and two rows ID=1). – Koffman

I don't recall posting it that way, but I changed it. – Lachance

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       

Unsteel

Three problems with this solution: 1) the data you INSERT into the tables is not the data specified in the question, 2) it requires that you strictly limit the list of colors at design time, which is unlikely and 3) it is a SQL Server solution, not a VFP solution (although I think the OP only added the Foxpro tags after you posted the solution). – Widgeon

I've taken data from the question, it looks like Harley edited it. Same regarding Foxpro... – Unsteel

Answer 2

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

Koffman

I think this solution will be very slow... so many subqueries. – Languid

@Languid The use of unique constraints on the Colors table can help improve the execution plan since it will be able to predict that the subquery is a scalar query. Either this, the pivot, or the other case Larry uses that would require 4 joins(or a join for every possible color). When you rotate data like this you often end up with some pretty slow queries. This could be avoided with better table design, but it sounds like the poster is limited to what the third party system already has implemented. I feel his pain cause this is a tough situation to be in. – Koffman

Answer 3

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.

Languid

Just need to correct the join conditions to "and Red.Color" and "and Blue.Color". – Snowslide

SQl Server 2005 and up do indeed have PIVOT and UNPIVOT. – Bromberg

Correct it does but you still end up strongly typing each column/variable you want to pivot on. MS Access has a way to dynamically create a pivot query based on values. That's what I was referring to by saying "like MS Access" – Languid

Answer 4

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.

Widgeon

There is no linking table, just the common ID. In table two we currently have 11 possible values for 'color' so each unique ID from table one could have up to 11 records in table two. – Lachance

Also, table one has 285,000 records and table two has 773,000 so creating a linking table prior to running a query may not be practical. As the tables are maintained through third party software getting them add the linking table to their code probably would never happen. – Lachance

It makes more sense now that you've updated the ID values in table 2 from 1, 2, 3, 4 to 1, 2, 2, 2. However, the output still does not match your table data since there's no Mary / Blue record in table 2. – Widgeon

Answer 5

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.

Bromberg