Question 20434 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL Workbench – Query for left join

I’m try to get the output of How many times each location is used for clus meetings, trainings, and competitions for my database. I figured out how to do each separately but am having trouble combining them into one output table.

SELECT l.LocId, l.Postcode, count(c.CId) FROM locations l left join competitions c
on l.LocId = c.LocId
GROUP BY LocId;

SELECT l.LocId, l.Postcode, count(t.TId) FROM locations l left join trainings t
on l.LocId = t.LocId
GROUP BY LocId;

SELECT l.LocId, l.Postcode, count(cl.Cno) FROM locations l left join clubs cl
on l.LocId = cl.LocId
GROUP BY LocId;

I’ve tried combining them using left join and can’t find an other ways to try

Tags:

2

Answers


  1. 0

    Assuming that LocId, CId, TId and Cno are the primary keys of the tables, do a LEFT join of locations to all 3 tables and aggregate with COUNT(DISTINCT ...):

    SELECT l.LocId, l.Postcode, 
       COUNT(DISTINCT c.CId) AS competitions_count,
       COUNT(DISTINCT t.TId) AS trainings_count,
       COUNT(DISTINCT cl.Cno) AS clubs_count
FROM locations l 
LEFT JOIN competitions c ON l.LocId = c.LocId
LEFT JOIN trainings t ON l.LocId = t.LocId
LEFT JOIN clubs cl ON l.LocId = cl.LocId
GROUP BY l.LocId;
    Login or Signup to reply.
  2. 0

    Just use subqueries in the SELECT clause:

    SELECT 
  l.LocId,
  l.Postcode,
  (SELECT count(c.CId) FROM competitions c WHERE c.LocId = l.LocId) as CId_count,
  (SELECT count(t.TId) FROM trainings t WHERE t.LocId = l.LocId) as TId_count,
  (SELECT count(cl.Cno) FROM clubs cl WHERE cl.LocId = l.LocId) as Vno_count
FROM locations l
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search