mardi 28 juin 2016

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


Aucun commentaire:

Enregistrer un commentaire