CODEWITHSUNDEEP
phpmysqllaraveleloquent
yayuj
yayuj

Reputation: 2464

Join three tables and count

This is really complicated, then I will say an example in order to make it easier.

Imagine that we have 4 tables: garages, cars, securities, places.

garages is where you can find the cars, securities is the security that is keeping that car safe inside of a garage, and places is where you can find garages similar to that one. Then we have a table like that.

garages table:

-----------------------------------
|garage_id|garage_name|garage_size|
-----------------------------------
|        1|   Garage 1|        200|
-----------------------------------
|        2|   Garage 2|        400|
-----------------------------------

cars table:

---------------------------
|car_id|car_name|garage_id|
---------------------------
|     1|   Car 1|        1|
---------------------------
|     2|   Car 2|        1|
---------------------------
|     3|   Car 3|        2|
---------------------------

securities table:

----------------------------------
|security_id|security_name|car_id|
----------------------------------
|          1|  Security 1|      1|
----------------------------------
|          2|  Security 2|      1|
----------------------------------
|          3|  Security 3|      2|
----------------------------------
|          4|  Security 4|      3|
----------------------------------

places table:

-------------------------------
|place_id|place_name|garage_id|
-------------------------------
|       1|   place 1|        1|
-------------------------------
|       2|   place 2|        1|
-------------------------------
|       3|   place 3|        1|
-------------------------------
|       4|   place 4|        2|
-------------------------------
|       5|   place 5|        2|
-------------------------------

What I want is to list the garages and join the three tables that is cars, securities and places like this:

Garage 1 has 2 cars with 3 securities and has 3 more garages similar

Garage 2 has 1 cars with 1 securities and has 2 more garages similar

Now you might ask, why Garage 1 has 3 securities? Because the Garage 1 has Car 1 and Car 2 and Car 1 has two securities Security 1 and Security 2 and Car 2 has 1 that is Security 3.

The problem here is: How to join the tables and count in one single query and return the result just like you can see above?

Upvotes: 0

Views: 76

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Simply join all tables, group by garage and count distinct matches:

select 
  g.garage_name, 
  count(distinct c.car_id) as count_cars,
  count(distinct s.security_id) as count_securities,
  count(distinct p.place_id) as count_places
from garages g
left join cars c on c.garage_id = g.garage_id
left join securities s on s.car_id = c.car_id
left join places p on p.garage_id = g.garage_id
group by g.garage_name
order by g.garage_name;

As to the desired output strings, concatenate your results: 

g.garage_name || ' has ' || count(distinct c.car_id) || ' cars with ' || ...

Upvotes: 2

axiac
axiac

Reputation: 72186

This query should do the job:

SELECT g.garage_id, g.garage_name, COUNT(c.car_id) AS nb_cars,
    COUNT(s.security_id) AS nb_securities, COUNT(p.place_id) AS nb_places
FROM garages g
    LEFT JOIN cars c ON g.garage_id = c.garage_id
    LEFT JOIN securities c.car_id = s.car_id
    LEFT JOIN places.p ON g.garage_id = p.garage_id
GROUP BY g.garage_id

You will get the list of all garages, including those that do not have cars or places around. If you want to filter out the garages without cars then use INNER JOIN cars .... To filter out the garages without places, use INNER JOIN places ....

Upvotes: 1

Marco Mura
Marco Mura

Reputation: 582

SELECT
 gb.garage_name,
 (
  SELECT COUNT(*) FROM cars_table ct WHERE ct.garage_id = db.garage_id
 ) as car_count,
 (
  SELECT COUNT(*) FROM security_table st JOIN cars_table ct ON ct.car_id = st.car_id WHERE ct.garage_id = db.garage_id
 ) as security_count,
FROM
garages_table gb
ORDER BY gb.garage_id

This is untested, but it's an idea.

Upvotes: 1

Related Questions