Tema: select nombres a partir de IDs separados por coma en una columna
Ver Mensaje Individual
  #1 (permalink)  
Antiguo 11/12/2012, 13:26
sisko
 
Fecha de Ingreso: junio-2007
Mensajes: 46
Antigüedad: 17 años, 5 meses
Puntos: 0
select nombres a partir de IDs separados por coma en una columna
Hola

Necesito ayuda para escribir una consulta bastante compleja:

Tengo una tabla 'producttags' con ID y Nombre para cada tag.
Y otra tabla 'products' con una columna 'tags' que contiene IDs de varios tags, separados por comas (ej: 20,38,400)
Lo que necesito es hacer un select que me devuelva una columna con el nombre de los tags de cada producto, si es posible separados por un "#"

Alguien sabe como puedo lograr esto?
Si no se entiende avisenme y intento explicarlo mejor.

Es para un sitio bastante grande y con mucho trafico asi que deberia ser lo mas eficiente posible.

Muchas gracias!


No se si es relevante, pero les copio la consulta actual:
Como podran ver, los tags actualmente se levantan usando la tabla 'productjointags', pero el objetivo de esta modificacion es no necesitar mas esa tabla


Código MySQL: 
Ver original
  1. select ProductID,Discontinued,Name,URL,
  2. (select group_concat(t.Name separator '#') from producttags t JOIN productjointags pt ON t.TagId=pt.TagId where pt.ProductID=p.ProductID) as Tags, Description,LeadTime,Size,PrintPos1,PrintSize1,Pri ntPos2,PrintSize2,PrintPos3,PrintSize3,Material,Ad ditionalInfo,Image1,Image2,Image3,Quantity1,Quanti ty2,Quantity3,Quantity4,Quantity5,Origination,
  3. (select group_concat(pp.Title separator '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as PriceNames,
  4. (select group_concat(CAST( pp.Price1 AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Price1,
  5. (select group_concat(CAST( pp.Price2 AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Price2,
  6. (select group_concat(CAST( pp.Price3 AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Price3,
  7. (select group_concat(CAST( pp.Price4 AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Price4,
  8. (select group_concat(CAST( pp.Price5 AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Price5,
  9. (select group_concat(CAST( pp.Margin AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Margins,
  10. (select group_concat(CAST( pp.Profit AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Profits,
  11. SpecialOffer,OfferExpiryDate,OfferDetails,
  12. (select Name from producttypes where TypeID=p.Type1) as Type1,
  13. (select Name from producttypes where TypeID=p.Type2) as Type2,
  14. (select Name from producttypes where TypeID=p.Type3) as Type3,
  15. (select Name from producttypes where TypeID=p.Type4) as Type4,
  16. (select group_concat(i.Name separator '#') from productindustries i JOIN productjoinindustries pi ON i.IndustryID=pi.IndustryID where pi.ProductID=p.ProductID) as Industries,
  17. (select group_concat(o.Name separator '#') from productoptions o JOIN productjoinoptions po ON o.OptionID=po.OptionID where po.ProductID=p.ProductID) as Options,
  18. (select group_concat(s.Name separator '#') from suppliers s JOIN productjoinsuppliers ps ON s.SupplierID=ps.SupplierID where ps.ProductID=p.ProductID ) as Suppliers,
  19. (select group_concat(ps.SupplierCode separator '#') from suppliers s JOIN productjoinsuppliers ps ON s.SupplierID=ps.SupplierID where ps.ProductID=p.ProductID) as SupplierCodes,
  20. (select group_concat(ps.Preferred separator '#') from suppliers s JOIN productjoinsuppliers ps ON s.SupplierID=ps.SupplierID where ps.ProductID=p.ProductID) as Preferred,
  21. (select group_concat(c.Name separator '#') from productcolours c JOIN productjoinpartcolours pjc ON c.ColourID=pjc.ColourID where pjc.ProductID=p.ProductID and pjc.PartID in (select pp.PartID from productparts pp where pp.Name='Main') Group by pjc.ProductID,pjc.PartID) as MainColours,
  22. (select group_concat(c.Name separator '#') from productcolours c JOIN productjoinpartcolours pjc ON c.ColourID=pjc.ColourID where pjc.ProductID=p.ProductID and pjc.PartID in (select pp.PartID from productparts pp where pp.Name='Barrel') Group by pjc.ProductID,pjc.PartID) as BarrelColours,
  23. (select group_concat(c.Name separator '#') from productcolours c JOIN productjoinpartcolours pjc ON c.ColourID=pjc.ColourID where pjc.ProductID=p.ProductID and pjc.PartID in (select pp.PartID from productparts pp where pp.Name='Trim') Group by pjc.ProductID,pjc.PartID) as TrimColours,
  24. (select group_concat(c.Name separator '#') from productcolours c JOIN productjoinpartcolours pjc ON c.ColourID=pjc.ColourID where pjc.ProductID=p.ProductID and pjc.PartID in (select pp.PartID from productparts pp where pp.Name='Body') Group by pjc.ProductID,pjc.PartID) as BodyColours,
  25. (select group_concat(c.Name separator '#') from productcolours c JOIN productjoinpartcolours pjc ON c.ColourID=pjc.ColourID where pjc.ProductID=p.ProductID and pjc.PartID in (select pp.PartID from productparts pp where pp.Name='Feet') Group by pjc.ProductID,pjc.PartID) as FeetColours,
  26. (select group_concat(c.Name separator '#') from productcolours c JOIN productjoinpartcolours pjc ON c.ColourID=pjc.ColourID where pjc.ProductID=p.ProductID and pjc.PartID in (select pp.PartID from productparts pp where pp.Name='Toy') Group by pjc.ProductID,pjc.PartID) as ToyColours,
  27. (select group_concat(c.Name separator '#') from productcolours c JOIN productjoinpartcolours pjc ON c.ColourID=pjc.ColourID where pjc.ProductID=p.ProductID and pjc.PartID in (select pp.PartID from productparts pp where pp.Name='Ribbon') Group by pjc.ProductID,pjc.PartID) as RibbonColours,
  28. (select group_concat(c.Name separator '#') from productcolours c JOIN productjoinpartcolours pjc ON c.ColourID=pjc.ColourID where pjc.ProductID=p.ProductID and pjc.PartID in (select pp.PartID from productparts pp where pp.Name='Tshirt') Group by pjc.ProductID,pjc.PartID) as TshirtColours,
  29. (select group_concat(c.Name separator '#') from productcolours c JOIN productjoinpartcolours pjc ON c.ColourID=pjc.ColourID where pjc.ProductID=p.ProductID and pjc.PartID in (select pp.PartID from productparts pp where pp.Name='Panel') Group by pjc.ProductID,pjc.PartID) as PanelColours,
  30. (select group_concat(c.Name separator '#') from productcolours c JOIN productjoinpartcolours pjc ON c.ColourID=pjc.ColourID where pjc.ProductID=p.ProductID and pjc.PartID in (select pp.PartID from productparts pp where pp.Name='Handle') Group by pjc.ProductID,pjc.PartID) as HandleColours,
  31. CreateDate,LastUpdateDate
  32. from products p where 1
;
Última edición por gnzsoloyo; 11/12/2012 a las 13:50 Razón: Código SQL sin etiquetar.