phpmyadmin Question posted in
The official documentation can be found here.

Check IDs from different tables – Phpmyadmin

I have two tables: “rota” (1) and “volunteer” (2). The first name and last name columns are in (2), but the shifts in (1). With the following query I am able to see the names of those who have submitted their shifts, but I would like to see those who have not. Can’t figure it out how. Any help?

SELECT COUNT(rota.shift_id), rota.volunteer_id, volunteer.firstname, volunteer.lastname
FROM rota 
INNER JOIN volunteer ON rota.volunteer_id=volunteer.id
GROUP BY volunteer_id;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This was the solution:

    SELECT volunteer.id, volunteer.firstname, volunteer.lastname, COUNT(rota.shift_id), rota.volunteer_id, volunteer.firstname, volunteer.lastname 
FROM volunteer 
LEFT JOIN rota ON rota.volunteer_id = volunteer.id 
GROUP BY volunteer.id
HAVING COUNT(rota.shift_id) = 0;

  2. 0

    You want a LEFT JOIN — and to put the volunteer table first because you want all the rows from that table:

    SELECT v.id, v.firstname, v.lastname, COUNT(r.shift_id), rota.volunteer_id
FROM volunteer v LEFT JOIN
     rota r 
     ON r.volunteer_id = v.id
GROUP BY v.id;

    Note that you do not need to include all columns from volunteer in the GROUP BY because v.id uniquely identifies each row in the result set.

    EDIT:

    If you want volunteers with no shifts, then use NOT EXISTS:

    SELECT v.*
FROM volunteer v
WHERE NOT EXISTS (SELECT 1
                  FROM rota r 
                  WHERE r.volunteer_id = v.id
                 );

    You can equivalently do this with LEFT JOIN:

    SELECT v.id, v.firstname, v.lastname
FROM volunteer v LEFT JOIN
     rota r 
     ON r.volunteer_id = v.id
WHERE r.volunteer_id IS NULL;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search