I've seen a few methods online to do this, but i couldn't manage to apply them in my case. I have 4 tables:
Suppliers(id_sup, name, city)
Companies(id_co, name, city)
Producuts(id_p, name, city)
Deliveries(id_sup, id_co, id_p)
What I need to do is do a query, where I get printed every city, only once, and for each city show the number of suppliers/companies/products that are there. I managed (somehow) to do this in Oracle SQL:
SELECT coalesce(s.city,c.city,p.city) city,
count(distinct s.id_sup) Suppliers,
count(distinct c.id_co) Companies,
count(distinct p.id_p) Products
FROM suppliers p
FULL OUTER JOIN companies c on s.city = c.city FULL OUTER JOIN products p on s.city=p.city
GROUP BY coalesce(s.city,c.city,p.city);
But I just don't know how to do this in Mysql. Any ideas?
