XaviEsteve XaviEsteve - 4 years ago 100
MySQL Question

Select users from one table only if not in another

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):

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?

Answer Source

Something like this?

    id NOT IN (SELECT 
        AND borrower = 1;

You might want to use different column names as I believe from and to are reserved words in mysql.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download