How can I specify the where clause when combining multiple tables?

Question

I have a query that should count all of my specified rows. This is my query:

SELECT COUNT(*) AS Som
     FROM users
     RIGHT JOIN stoftesten ON users.username = stoftesten.gebruikersid
     RIGHT JOIN eindgesprekken ON users.username = eindgesprekken.gebruikersid
     RIGHT JOIN rapportages ON users.username = rapportages.gebruikersid
     WHERE users.rol = 'geleider' and users.basis = '1' and stoftesten.status = 'Niet beoordeeld' or eindgesprekken.status = 'Niet beoordeeld' or rapportages.status = 'Niet beoordeeld';

I should get the number 2, because thats the amount of records I have according to the query I want, but it gives me 40. What am I doing wrong here?

Tables:

users

-------------------------------------------
| username (PK) | basis | rol      | name |
-------------------------------------------
| user01        | VLB   | geleider | Ben  |
| user02        | GER   | geleider | Tom  |
-------------------------------------------

stoftesten

-------------------------------------------
| id (PK) | gebruikersid (FK:id) | status |
-------------------------------------------
| 1       | user01   | Niet beoordeeld    |
| 2       | user01   | Beoordeeld         |
-------------------------------------------

rapportages

-------------------------------------------
| id (PK) | gebruikersid (FK:id) | status |
-------------------------------------------
| 1       | user01   | Beoordeeld         |
| 2       | user01   | Beoordeeld         |
-------------------------------------------

eindgesprekken

-------------------------------------------
| id (PK) | gebruikersid (FK:id) | status |
-------------------------------------------
| 1       | user01   | Niet beoordeeld    |
| 2       | user01   | Beoordeeld         |
-------------------------------------------

I want the query to select the records from 'stoftesten', 'eindgesprekken' and 'rapportages' where the status is 'Niet beoordeeld' and the user basis is 'VLB'. Then I want to count these rows and get a number. in this case, I want to see the number 2.

Maybe you should use parenthesis for your `and` / `or` combinations.. [see here for examples of precedence](https://stackoverflow.com/questions/12345569/mysql-or-and-precedence) — Kaddath, Jan 15 '19 at 09:26
Could you show your expected result from that sample data, and the logic how to get that result? — Pham X. Bach, Jan 15 '19 at 09:30

score 0 · Answer 1 · answered Jan 15 '19 at 11:52

I suspect you want to count users that meet any of those conditions. If so, I recommend EXISTS rather than joins:

SELECT COUNT(*) AS Som
FROM users u
WHERE u.rol = 'geleider' and u.basis = '1' AND 
      (EXISTS (SELECT 1
               FROM stoftesten s
               WHERE s.gebruikersids = u.username AND
                     s.status = 'Niet beoordeeld'
              ) OR
       EXISTS (SELECT 1
               FROM eindgesprekken e
               WHERE e.gebruikersids = u.username AND
                     e.status = 'Niet beoordeeld'
              ) OR
       EXISTS (SELECT 1
               FROM rapportages r
               WHERE r.gebruikersids = u.username AND
                     r.status = 'Niet beoordeeld'
              )
      );

How can I specify the where clause when combining multiple tables?

1 Answers1