select nombres a partir de IDs separados por coma en una columna

[sisko]

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:

select ProductID,Discontinued,Name,URL,
(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,
(select group_concat(pp.Title separator '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as PriceNames,
(select group_concat(CAST( pp.Price1 AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Price1,
(select group_concat(CAST( pp.Price2 AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Price2,
(select group_concat(CAST( pp.Price3 AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Price3,
(select group_concat(CAST( pp.Price4 AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Price4,
(select group_concat(CAST( pp.Price5 AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Price5,
(select group_concat(CAST( pp.Margin AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Margins,
(select group_concat(CAST( pp.Profit AS CHAR ) SEPARATOR '#') from productprices pp where pp.ProductID=p.ProductID Group by pp.ProductID) as Profits,
SpecialOffer,OfferExpiryDate,OfferDetails,
(select Name from producttypes where TypeID=p.Type1) as Type1,
(select Name from producttypes where TypeID=p.Type2) as Type2,
(select Name from producttypes where TypeID=p.Type3) as Type3,
(select Name from producttypes where TypeID=p.Type4) as Type4,
(select group_concat(i.Name separator '#') from productindustries i JOIN productjoinindustries pi ON i.IndustryID=pi.IndustryID where pi.ProductID=p.ProductID) as Industries,
(select group_concat(o.Name separator '#') from productoptions o JOIN productjoinoptions po ON o.OptionID=po.OptionID where po.ProductID=p.ProductID) as Options,
(select group_concat(s.Name separator '#') from suppliers s JOIN productjoinsuppliers ps ON s.SupplierID=ps.SupplierID where ps.ProductID=p.ProductID ) as Suppliers,
(select group_concat(ps.SupplierCode separator '#') from suppliers s JOIN productjoinsuppliers ps ON s.SupplierID=ps.SupplierID where ps.ProductID=p.ProductID) as SupplierCodes,
(select group_concat(ps.Preferred separator '#') from suppliers s JOIN productjoinsuppliers ps ON s.SupplierID=ps.SupplierID where ps.ProductID=p.ProductID) as Preferred,
(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,
(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,
(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,
(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,
(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,
(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,
(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,
(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,
(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,
(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,
CreateDate,LastUpdateDate
from products p where 1
 

;

[Heimish2000]

Lo que tienes es un problema clarísimo de diseño en tu base de datos. No se deben guardar en un mismo campo varios datos, como haces tú con los tags. Lo mejor es que crees una tabla que relacione productos y tags.