[solved] sql question - how to get manufacturers details?

[prolivesound]

Hi

Can anyone help me with sql question, I want manufacturers and not just id_manufactur my sql question looks like this

 

 

 

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.reference, p.id_manufacturer

FROM ps_product p

LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)

LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)

LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)

WHERE pl.id_lang = 49

AND cl.id_lang = 49

GROUP BY p.id_product

 

 

Please

 

 

I found a solution it should look like this

 

 

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.reference,m.name AS manufacturer

FROM ps_product p

LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)

LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)

LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)

LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)

WHERE pl.id_lang = 49

AND cl.id_lang = 49

GROUP BY p.id_product

 

 

id_lang = 49 for Swedish

id_lang = 1 for English

Edited July 3, 2013 by vekia (see edit history)

[vekia]

what you mean by "want manufacturers" ? can you clarify what you exactly expect?

[prolivesound]

I want manufactur name and not just id_manufactur

[razaro]

Try this

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.reference, m.name as 'manufacturer'
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
WHERE pl.id_lang = 49
AND cl.id_lang = 49
GROUP BY p.id_product

[prolivesound]

Try this

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.reference, m.name as 'manufacturer'
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
WHERE pl.id_lang = 49
AND cl.id_lang = 49
GROUP BY p.id_product

Thanks I finally found it I

[vekia]

thanks for solution

now i can mark this thread as [solved]

if you've got any other questions related to this query - feel free to continue discussion here

 

regards

[surin]

Try this

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.reference, m.name as 'manufacturer'
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)
WHERE pl.id_lang = 49
AND cl.id_lang = 49
GROUP BY p.id_product

Hello Dear,

i got this string and it works very well.

 

But i need another string for my requirments:

 

I Want product id, product name, product attribute, product feature , product price, product final price after tax, category,

 

i need this very urgent kindly help me for this

[vekia]

Hello Dear,

i got this string and it works very well.

 

But i need another string for my requirments:

 

I Want product id, product name, product attribute, product feature , product price, product final price after tax, category,

 

i need this very urgent kindly help me for this

most of the fields you wanted you've got now in this query.

you need to add also query for attributes, but it will not be as easy as it seems.

i think that it will be much easier to create new query in this case

[PascalVG]

surin,

I suggest you make a new topic for this last question, as this topic is marked as solved (as the title question is solved already)

and not many people may look in it anymore.

 

pascal