joining tables

[pixelman111]

I have two tables: produkty and zamowienia2

 

I want to join these two tables in the way that will show me full list of products (from the ("produkty") and will show how many item of each product where ordered (table "zamowienia2") in given record (displaying 0 next to the not ordered products). Tables structure:

 

products:

idprod (PK)

naglowek_pl

linkjavascript:void(0);

Insert Table

cena_pl

male

rabat

data

zawartosc

txt_prom

typ

koszyk

 

zamowienia2

id_zam (PK)

id_prod (product id)

l_sztuk (number of items)

 

query:

SELECT DISTINCT zamowienia2.id_prod, zamowienia2.l_sztuk, produkty.idprod, produkty.naglowek_pl as nazwa, produkty.link as link, produkty.cena_pl as cena, produkty.male as foto, produkty.rabat, produkty.data, produkty.zawartosc as zawartosc, produkty.txt_prom as txt_prom, produkty.typ, produkty.koszyk FROM zamowienia2 RIGHT JOIN produkty ON zamowienia2.id_prod=produkty.idprod WHERE zamowienia2.id_zam='$idzam' AND produkty.archiwum='n' AND produkty.naglowek_pl!='' ORDER BY produkty.sort_koszyk asc, produkty.linia asc, produkty.naglowek_pl asc";

 

It worsk fine, but displays only these products which are in given order number. I want it to display full list of products and mark only those who were ordered by displaying the number of ordered items. Any sugestions?

 

[Wildbug]

SELECT produkty.*,IFNULL(zamowienia2.l_sztuk, 0) AS order_count
FROM produkty LEFT JOIN zamowienia2 ON produkty.idprod=zamowienia2.id_prod;

[pixelman111]

Thanks, but I'm afraid that this query does exactly the same as this one I wrote in the first post (it generates the same result).

 

I want to list all products, not only ordered, but I also want to know how many items of each product were ordered too.

 

[fenway]

Just add a COUNT() and a group by.

[Wildbug]

Perhaps I don't understand your table design or desired result.

 

Here's what I'm assuming:  all of your items are in "produkty"; all of your orders are in "zamowienia2"; tables are related by produkty.idprod=zamowienia2.prod_id; you want a list of ALL products (in produkty) and the amount that are ordered, even if zero.  The other thing that I assumed, which is probably wrong, was that zamowienia2 only contained one entry per prod_id.

 

Try this:

 

SELECT produkty.idprod, IFNULL(SUM(zamowienia2.l_sztuk),0) AS Quantity
FROM produkty LEFT JOIN zamowienia2 ON produkty.idprod=zamowienia2.prod_id 
GROUP BY produkty.idprod;

 

Add whatever other columns you need to the SELECT... part.