Query to show products and manufacturer

[DARKF3D3]

I need a query to show a list of all products, with id-product, product name and manufacturer.

 

Example:

1          iPhone 5          Apple

2          iPhone 4s          Apple

3          Galaxy S4          Samsung

4          Galaxy S4 Mini          Samsung

5          ...          ...

6          ...          ...

 

 

Someone could help me? I'm not so good with sql.

 

 

 

 

 

 

PS: I'm using prestashop 1.4.9

[DARKF3D3]

No one know which query i can use to do that?

[vekia]

select p.id_product, pl.name,ps.name from ps_product AS p
INNER JOIN ps_product_lang AS pl ON p.id_product = pl.id_product
LEFT JOIN ps_supplier AS ps ON p.id_supplier = ps.id_supplier
WHERE pl.id_lang=1

effect:

[DARKF3D3]

Thanks vekia, using your code i finally got the list, i change supplier with manufacturer, because i need to know the manufacturer and not the supplier.

 

Here's my code:

select p.id_product, pl.name,ps.name from ps_product AS p
INNER JOIN ps_product_lang AS pl ON p.id_product = pl.id_product
LEFT JOIN ps_manufacturer AS ps ON p.id_manufacturer = ps.id_manufacturer
WHERE pl.id_lang=1

Thanks a lot

[DARKF3D3]

I notice a strange things using my last query i posted, few products (like 10 on a catalogue of 500) showed in the list have wrong product name... I don't understand how it's possible.

[vekia]

try to change id_lang field to your language, can you chec results then?

[DARKF3D3]

Ok, I found the problem.

Sometimes, to add at catalog a new product, i copy an existing product, and after i change it's name, but when i change it, in the other language it maintain the old name.

It's isn't possible to force the same name for every language or disable the other languages? In fact i don't use them.

[DARKF3D3]

Now I'm trying to modify the query to show only the products with no manufactured, any help?

[vekia]

select p.id_product, pl.name,ps.name from ps_product AS p

INNER JOIN ps_product_lang AS pl ON p.id_product = pl.id_product

LEFT JOIN ps_manufacturer AS ps ON p.id_manufacturer = ps.id_manufacturer

WHERE pl.id_lang=1 AND p.id_manufacturer = ''

 

[werklich]

nice query, thanks :-)

 

what about sold item + manufacturer in one query? does it work also?

 

 

 

Version: 1.5.6.1

[vekia]

sold item - you mean number of sold items, or just ordered products list based on "manufacturer" ?

[werklich]

think i got - look this way, to get all informations:

SELECT * 
FROM `ps_order_detail` AS od 
INNER JOIN `ps_product` AS p ON od.product_id = p.id_product 
LEFT JOIN ps_manufacturer AS ps ON p.id_manufacturer = ps.id_manufacturer

backround: the result of the sql query will be imported by excel, qlikview e.g, to send e-mails to manufacturer about ordering; manufacturer will send invoice, delivery note etc. to customer and gets the cash, we get commission.

some products must be delivered by manufacturer himself (e.g. container, stairways) so it's a must have to know the manufacturer by each sold product. in this case the handling about delivering is an other great problem :-)