MySQL count all records that have at least one record on a pivot table

33
April 16, 2019, at 00:20 AM

I'm trying to look if a record of a table, has at least one record on a pivot table that is used on a many to many relationship.

The table Attendant is:

| ID |  NAME  |
|----|--------|
| 1  | Name A |
| 2  | Name B |
| 3  | Name C |

And the Attendant_Event pivot table has the following structure

| ID |  attendant_id  |  event_id  |  uuid  |
|----|----------------|------------|--------|
| 1  |       1        |      1     |   xxx  |
| 2  |       1        |      2     |   yyy  |
| 3  |       3        |      1     |   zzz  |
| 4  |       3        |      2     |   www  |
| 5  |       1        |      3     |   xyx  |
| 6  |       3        |      3     |   rer  |

My query is trying to count the attendants that has a least one record on the pivot table, but count all the records as one. For example, the expected result will be a table like this:

| STATUS |  COUNT |
|--------|--------|
|   YES  |    2   | 
|   NO   |    1   |

This results are expected because:

  1. Only the Attendants with ID 1 and 3 has a record on the Attendant_Event table. This told us that the number of Attendants with rows on the pivot table is 2.
  2. The Attendant with ID 2 has no records, so the number of attendants with no records on the pivot table is 1.

By now, my query is the following:

SELECT IF(uuid <=> NULL, 'NO', 'YES') as status, count(*) as count FROM attendants att LEFT JOIN attendant_event ae ON ae.attendant_id = att.id GROUP BY status

But this is showing me a result like this.

| STATUS |  COUNT |
|--------|--------|
|   YES  |    6   | 
|   NO   |    1   |

This means that, count each of the rows. If we take the previous example, both Attendants with id 1 and 3 has 3 records on the pivot table. So It gives 6 instead of the two that I'm looking for.

What I'm doing wrong?

Answer 1

When you make left join, you create intersection, that is larger than attendants table. Your join consists of rows with repeating attendant_id and different event uuid.

You can watch the intersection by executing SELECT IF(uuid <=> NULL, 'NO', 'YES') as status, att.id, ae.uuid FROM attendants att LEFT JOIN attendant_event ae ON ae.attendant_id = att.id. It includes 7 rows, 6 of them are with YES events for two active attendats and 1 row with NO events.

So you should count only distinct values:

SELECT IF(uuid <=> NULL, 'NO', 'YES') as status, count(distinct(att.id)) as count 
   FROM attendants att 
   LEFT JOIN attendant_event ae ON ae.attendant_id = att.id 
   GROUP BY status
Answer 2

You may want to select the attendant IDs with their respective YES/NO first, then count them, something like:

SELECT status, count(distinct attendant_id) as count FROM (
   SELECT IF(ae.uuid IS NULL, 'NO', 'YES') as status, ae.attendant_id
   FROM attendants att LEFT JOIN attendant_event ae ON ae.attendant_id = att.id
   GROUP BY ae.attendant_id) x
GROUP BY status
READ ALSO
table inner join not displaying expected results

table inner join not displaying expected results

I am trying to get a query to display some resultsI double checked the logic to make sure that it is correct and it looks like it should work to me but does not

18
Combining results from two tables into JSON data

Combining results from two tables into JSON data

I have two tables, words and paragraphThe words table is as below:

29
How to query the unmodified timestamp inside a JSON object using MySQL?

How to query the unmodified timestamp inside a JSON object using MySQL?

I would like to query a JSON object containing a MySQL TIMESTAMP named 'time':

27
Multiple row INSERT from a single SELECT result

Multiple row INSERT from a single SELECT result

I am inserting multiple rows into a table, using the result from a subquery :

7