Jump to content

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


prolivesound
 Share

Recommended Posts

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 by vekia (see edit history)

Share this post


Link to post
Share on other sites

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

  • Like 2

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

  • 2 months later...

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More