left or right joins help needed

rikblank

Hi,
I\'ve two tables from which I want to know the number of products in each, by name.

This is what I got so far:

SELECT COUNT(p.prodnaam), p.prodnaam FROM orders o, old_orders oo, producten p
WHERE o.artnr = p.artnr
AND oo.artnr = p.artnr
GROUP BY p.prodnaam

The problem is that it only shows the products which excist in each table. Thus when product1 in table orders excists not in old_orders it will not be shown.
I\'ve read something about left en right join but I don\'t know how to handle these with this peace of SQL.

Does someone can give me a hint?

Kind Regards,
Rik

Anon

select count(*),prodnaam from producten p
join ( select artnr from orders
union all
select artnr from old_orders ) as s
on p.artnr = s.artnr
group by prodnaam

smarlowe

OK, left joins:

For each row in the "left" table, we will look for a match in the right table. If a match is found, we will deliver the row with the information from both. If no match is found, we will deliver the row with the data from the left table, and the fields from the right table set to nulls. Right joins are the same, just switching which table to treat this way.

So, suppose we have a product table with 10 products, and only 8 of these have orders in the order table.

select * from products p left join orders o on p.itemno=o.itemno;

will return EVERY product in the product table, and their matching order(s).

Entries in the order table with no match in products will not be shown.

rikblank

Thanks for your comment but I got an syntax error near '(' ...

Regards,
Rik

smarlowe

What database are you using?

rikblank

I'm using mysql 3.23.33 Didn't even know you could use subqueries in mysql. I know them only from Oracle

rikblank

SELECT COUNT(p.prodnaam), p.prodnaam FROM producten p
left join orders o on p.artnr=o.artnr
left join old_orders oo on p.artnr=oo.artnr
GROUP BY p.prodnaam

This is what I got so far. It return every record, but also the ones that don't excists in the tables orders & old_orders.

Where getting closer...gonne try something different with this query

smarlowe

You can't. But it might support left / right joins. I use postgresql because I hate the limited nature of MySQL too much to put up with it.