How can I join 3 tables in MySQL?

I have these 4 tables:

Suppliers(id_sup, name, city)
Companies( id_co, name, city)
Products(id_p, name, city)
Deliveries (id_sup, id_co, id_p)

in one query, I need to get a list of all cities (no duplicates) and for each, show the number of suppliers, companies, and products that can be found in that city.

In oracle SQL I would have used some Full OUTER JOIN. what's the alternative?

This is a suggested solution for joining 2 of the tables:

SELECT city                                          
     , COUNT(DISTINCT id_sup) Suppliers                   
     , COUNT(DISTINCT id_co) Companies                    
FROM ( SELECT COALESCE(s.city,c.city) city     
              , id_sup                           
              , id_co                            
         FROM Suppliers AS s                   
            LEFT OUTER JOIN Companies AS c ON c.city = s.city                  
         UNION                                   
         SELECT COALESCE(s.city,c.city) city     
              , id_sup                           
              , id_co                            
         FROM Suppliers AS s                   
            RIGHT OUTER JOIN Companies AS c ON c.city = s.city ) AS union_query

GROUP BY city 

How to add the final table Products to the mix?

With UNION get all the cities from all 3 tables and LEFT join to the results the 3 tables to finally aggregate:

select t.city,
  count(distinct s.id_sup) counter_suppliers,
  count(distinct c.id_co) counter_companies,
  count(distinct p.id_p) counter_products
from (
  select city from suppliers union  
  select city from companies union
  select city from products 
) t
left join suppliers s on s.city = t.city
left join companies c on c.city = t.city
left join products p on p.city = t.city
group by t.city

See a simplified demo.<br/>

to get all cities, since we don't have a city table as a dimension, we can get the domain from each of the city columns from each of the three tables, and combine them with UNION set operator:

SELECT cs.city
  FROM suppliers cs
 GROUP BY cs.city 

 UNION

SELECT cc.city 
  FROM companies cc
 GROUP BY cc.city 

 UNION

SELECT cp.city
  FROM products cp
 GROUP BY cp.city

That should get us a list of distinct city values that appear in the three tables.

We could take that set, and do outer join operations to the individual tables. But that has the potential to generate cross product... if there are three suppliers related to a city and four companies related to the same city, we would generate a resultset of twelve rows.

To fix that, we could get a count of DISTINCT primary key values.

Or, we can pre-aggregate the results in inline views, returning a single row per city. That avoids the problem of semi-cartesian products.

Let's reference the query above in an line view in another query. We will alias it ci. (If we had a dimension table city, we could reference that,)

Something like this:

SELECT ci.city 
     , IFNULL(np.cnt_,0)   AS cnt_products
     , IFNULL(nc.cnt_,0)   AS cnt_companies
     , IFNULL(ns.cnt_,0)   AS cnt_suppliers

  FROM ( /* inline view query */ ) ci

  LEFT
  JOIN ( SELECT p.city
              , COUNT(1) AS cnt_
           FROM products p
          GROUP BY p.city
       ) np
    ON np.city = ci.city

  LEFT
  JOIN ( SELECT c.city
              , COUNT(1) AS cnt_
           FROM companies c
          GROUP BY c.city
       ) nc
    ON nc.city = ci.city

  LEFT
  JOIN ( SELECT s.city
              , COUNT(1) AS cnt_
           FROM suppliers s
          GROUP BY s.city
       ) ns
    ON ns.city = ci.city

 ORDER BY ci.city

(In place of /* inline view query */, use the SQL text from the first query, to generate the distinct list of city.)

Just use union all and group by:

select city, sum(is_supplier), sum(is_company),
       sum(is_product), sum(is_delivery)
from ((select city, 1 as is_suppler, 0 as is_company, 0 as is_product, 0 as is_delivery
       from suppliers
      ) union all
      (select city, 0 as is_suppler, 1 as is_company, 0 as is_product, 0 as is_delivery
       from companies
      ) union all
      (select city, 0 as is_suppler, 0 as is_company, 1 as is_product, 0 as is_delivery
       from products
      ) union all
      (select city, 0 as is_suppler, 0 as is_company, 0 as is_product, 1 as is_delivery
       from deliveries
      ) 
     ) c
group by city;

Or, even more simply in MySQL:

select city, sum(which = 'supplier'), sum(which = 'company'),
       sum(which = 'product'), sum(which = 'delivery')
from ((select city, 'suppler' as which from suppliers
      ) union all
      (select city, 'company' as which from companies
      ) union all
      (select city, 'product' as which from products
      ) union all
      (select city, 'delivery' as which from deliveries
      ) 
     ) c
group by city;