Jump to content

How can i add the Supplier Name in this querry ?


Yankee Store

Recommended Posts

Hello iam looking to have my stock inventory  with the following querry, howver i cant seem to be able to figure out how to get the actual supplier name from my querry.  what do i need to do ?
thank you for your help 

 

SELECT 
p.id_product as 'Prod Id',
p.id_supplier as 'Supplier ID Number',


p.id_supplier as 'Supplier Name',


p.id_category_default as 'Category Number',
cl.name as 'Product Category',
    p.reference as 'Product Reference', 
    pl.name as 'Product Name', 
    if(sa.quantity is null, 0, sa.quantity) as `Quantité - In Stock`, 
    '' as `Réel`,
    if(p.active = 0,'Non','Oui') as `Activé`, 
round(ps.wholesale_price,2) as `Unit Cost`, 
if(sa.quantity is null, 0, round(sa.quantity*ps.wholesale_price,2)) as `Total Products -  Cost`, 
round(ps.price,2) as `Prix Vente / Unité`, 
if(sa.quantity is null, 0, round(sa.quantity*ps.price,2)) as `Total Prod Vente`
FROM ps_product p
INNER JOIN ps_product_shop ps ON (ps.id_product = p.id_product)
INNER JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
LEFT JOIN  ps_stock_available sa ON (p.id_product = sa.id_product)
LEFT JOIN  ps_category_lang  cl ON p.id_category_default = cl.id_category

WHERE pl.id_lang = 1

Link to comment
Share on other sites

Hello:

You need to join with ps_supplier table:

 SELECT 
p.id_product as 'Prod Id',
p.id_supplier as 'Supplier ID Number',


sup.name as 'Supplier Name',


p.id_category_default as 'Category Number',
cl.name as 'Product Category',
    p.reference as 'Product Reference', 
    pl.name as 'Product Name', 
    if(sa.quantity is null, 0, sa.quantity) as `Quantité - In Stock`, 
    '' as `Réel`,
    if(p.active = 0,'Non','Oui') as `Activé`, 
round(ps.wholesale_price,2) as `Unit Cost`, 
if(sa.quantity is null, 0, round(sa.quantity*ps.wholesale_price,2)) as `Total Products -  Cost`, 
round(ps.price,2) as `Prix Vente / Unité`, 
if(sa.quantity is null, 0, round(sa.quantity*ps.price,2)) as `Total Prod Vente`
FROM ps_product p
INNER JOIN ps_product_shop ps ON (ps.id_product = p.id_product)
INNER JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
LEFT JOIN  ps_stock_available sa ON (p.id_product = sa.id_product)
LEFT JOIN  ps_category_lang  cl ON p.id_category_default = cl.id_category

LEFT JOIN ps_supplier sup ON p.id_supplier = sup.id_supplier

WHERE pl.id_lang = 1 

Regards

Link to comment
Share on other sites

  • 6 months later...

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
×
×
  • Create New...