CODEWITHSUNDEEP
phpmysql
Caedes
Caedes

Reputation: 23

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

Upvotes: 1

Views: 63

Answers (3)

Kickstart
Kickstart

Reputation: 21513

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.

Upvotes: 0

Dragutin Ćirković
Dragutin Ćirković

Reputation: 41

Not sure what output you expect but this lists all users and all demands for them:

SELECT u.id, o.id_demand_fk FROM users u  LEFT JOIN offers o on o.id_user_fk = u.id  WHERE o.id_demand_fk <> 999 OR u.id is null

As you can see i removed group becouse group will always show only one group (your problem with group 1). After this query you can parse results using array to assign multiple offers to single user so you can get an array with users and assigned offers.

Upvotes: 2

Michal Bieda
Michal Bieda

Reputation: 939

Maybe it is not beautiful to use a sub query but this should do the job:

$sql = "SELECT u.id FROM users u WHERE u.id NOT IN (SELECT o.id_user_fk FROM offers o WHERE o.id_demand_fk = {$demandId})";

Upvotes: 0

Related Questions