Query Question [modified]

[Sean G Murphy]

I have three tables Item(Item_ID, Item_Name) 1 shirt 2 hat Color(Color_ID, Color_Name) 1 blue 2 red 3 green Item_Color(Item_Color_ID, Item_ID, Color_ID) 1 1 1 2 1 2 3 2 1 4 2 2 5 2 3 What is the query that would give me the result: Item_ID Item_Name Color_Name1 Color_Name2 Color_Name3 1 shirt blue red null 2 hat blue red green I seem to continue to come up short. I tried: Select Item_Name, GROUP_CONCAT(DISTINCT Item_Color) FROM Item Left Join Item_Color On Item_Color.Item_Id=Item.Item_ID Left Join Color ON Item_Color.Color_ID=Color.Color_ID Group By Item_Name but this returns the colors in a blue,red,green format, not in seperate columns. -- modified at 20:01 Tuesday 24th July, 2007

Thanks! Sean Murphy "All things great and small start at the same point, the first step."

[andyharman]

Hi Sean The following would give you the results that you asked-for:

select I.Item_ID, I.ItemName,
  case when Blue.Item_ID is not null then 'Blue' else null end,
  case when Red.Item_ID is not null then 'Red' else null end,
  case when Green.Item_ID is not null then 'Green' else null end
from Item I
left outer join Item_Color Blue
  on Blue.Item_Id = I.Item_Id
  and Blue.Color_Id = 1
left outer join Item_Color Red
  on Red.Item_Id = I.Item_Id
  and Red.Color_Id = 2
left outer join Item_Color Green
  on Green.Item_Id = I.Item_Id
  and Green.Color_Id = 3
order by I.Item_ID

However, it would need to be amended to handle additional colors. If you're using SQL-Server 2005 then you could use the new Pivot[^] query operator. Regards Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".