Reputation: 1162
We have two tables:
-- users --
id name borrower
-- ------ --------
1 Peter 1
2 John 1
3 Mark 1
4 David 0
-- lendings --
id from to amount -- ---- -- ------ 1 1 2 100
I need to output users that Peter hasn't lent any money and that are borrowers=1 (excluding Peter from the results), like this:
id name borrower
-- ------ --------
3 Mark 1
Right now I am stuck with this query (doesn't work):
SELECT *
FROM `users` u
LEFT OUTER JOIN `lendings` l
ON u.`id` = l.`from`
WHERE l.`from` is null
AND u.`id` != 1
AND u.`borrower` = 1
This is a simplified example of the actual code to keep the question useful/readable for future readers.
Related question: MySQL: select emails from one table only if not in another table?
Upvotes: 1
Views: 2412
Reputation: 1127
Something like this?
SELECT
*
FROM
users
WHERE
id NOT IN (SELECT
id
FROM
lendings)
AND borrower = 1;
You might want to use different column names as I believe from and to are reserved words in mysql.
Upvotes: 3