SQL Join Tables

Question

Table contains

ID|Name 1 Mary 2 John 

Table 2 contains

 ID|Color 1 Red 2 Blue 2 Green 2 Black 

I want to finish with

 ID|Name|Red|Blue|Green|Black 1 Mary YY 2 John YYY 

Thanks for any help.

---

Thanks for answers. I am going to republish this with additional information about what exactly I'm trying to do, which can complicate this. Can someone close this?

Answer 1

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

Here is the 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 the conclusion:

 John YYY Mary YY 

Answer 2

I can use the CASE statement with a subquery to enter 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 3

I think you will need 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 such as MS Access.

Answer 4

As other commentators note, you don’t show exactly how you connect people and colors. If you use a reference table (person_id, color_id), then there is no way to solve this problem in standard SQL, because it requires summary or cross-tabulation, which is not part of standard SQL.

If you want to add the condition that the number of colors is limited and known, and the development time, you can come up with a solution using one connection for each color, as well as the CASE or IF functions in SQL. But this would not be elegant, and, in addition, I would not trust this state in order to remain faithful for a very long time.

If you can come up with a different way of storing information about color binding, you may have more options to create the result you want, but another method of storage involves some degree of database denormalization, which can lead to other difficulties.

Otherwise, you will need to do this in the stored procedure or application code.

Answer 5

Unlike other posters; I do not see the need for a third table. If colors are a well-known enumeration in your application, you do not need a Color table.

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