Caedes Caedes - 5 months ago 11
MySQL Question

Select records from one table that are not in another table but with specific conditions

I have three tables, let's call them

offers
,
users
and
demands
.

Table users
id | name
1 A
2 B
3 C

Table demands
id | id_user_fk
1 1
2 2
3 3

Table offers
id | id_demand_fk | id_user_fk
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 3


Here is my problem. The purpose is to assign users to demands in order to let them post offers. When I assign these users, I've a bootstrapTable that allows me to write in the
offers
table.

Here is the query I made to get the list of users :

SELECT u.id "
. "FROM users u "
. "LEFT JOIN offers o on o.id_user_fk = u.id "
. "WHERE o.id_demand_fk <> " . $id . " OR u.id is null "
. "GROUP BY u.id"


The purpose is to ONLY show users that are not already assigned to the offer (which is why I use an $id). Problem is, users 1, 2 and 3 are assigned to both demands 1 and 2, so when I open the view that should show users that can be assigned to demand 2, I do have users 1, 2 and 3 because they're assigned to demand 1. My query doesn't filter that, and I've no clue how to do it.

Thank you in advance

Answer

Normally this is done with a LEFT OUTER JOIN (as you have done), but with the specific check done in the ON clause of the JOIN rather than just the WHERE clause. Then you check for a NULL field in the table that has been LEFT OUTER JOINed to check no match has been found:-

SELECT u.id 
FROM users u 
LEFT OUTER JOIN offers o ON o.id_user_fk = u.id AND o.id_demand_fk = " . $id . "
WHERE o.id IS NULL 

An IN with a sub query is possible, but tends to perform badly as volumes rise.

Comments