SQL LEFT JOIN for all records of first table and matched records of another table

Question

I have 3 tables.

Table seller with columns like id, name etc.
Table customer like id, name etc.
Table connections which have seller_id, customer_id, status of friendship like "friends", "pending_request" etc.

Now I want to get all the sellers who are not friends of a specific customer.

So I tried like fetching records from seller table with left join of connections table, with condition status is not "friends"

I tried the following query but didn't help me. I also tried other queries but didn't help.

SELECT * FROM `seller` LEFT JOIN `connections` ON seller.user_id = connections.user_id WHERE customer_id = 10 AND request_status NOT LIKE "friends"

Here is the reference screen I want the result. Like for a particular customer, all the sellers who are not friends or request is pending.

What issue you are facing? And please add some sample data from different tables if possible. — mkRabbani, Nov 21 '19 at 11:17
Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. — philipxy, Nov 21 '19 at 11:18
This is a faq. [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS "But didn't work" is not helpful. [ask] — philipxy, Nov 21 '19 at 11:20
"_I tried the following query but didn't help me. But didn't work. I also tried other queries but didn't help._" <- This? is not a problem description. Explain **what** you tried and **why** it did not achieve the desired result. — underscore_d, Nov 21 '19 at 11:26

score 1 · Accepted Answer · answered Nov 21 '19 at 11:21

Join the connections of type 'friends' for customer_id = 10 and in a WHERE clause check for the connections.user_id being NULL, i.e. nothing has been joined.

SELECT *
       FROM seller
            LEFT JOIN connections
                      ON seller.user_id = connections.user_id
                         AND connections.customer_id = 10
                         AND connections.request_status = 'friends'
       WHERE connections.user_id IS NULL;

Or use a correlated subquery, that gets the connection with a NOT EXISTS.

SELECT *
       FROM seller s
       WHERE NOT EXISTS (SELECT *
                                FROM connections c
                                WHERE c.user_id = s.user_id
                                      AND c.customer_id = 10
                                      AND c.request_status = 'friends');

score 0 · Answer 2 · answered Nov 21 '19 at 11:17

Try this:

SELECT * FROM `seller` LEFT JOIN `connections` ON seller.user_id = connections.user_id WHERE customer_id = 10 AND (request_status NOT LIKE "friends"
or request_status is null)

Or this:

SELECT * FROM `seller` LEFT JOIN `connections` ON seller.user_id = connections.user_id WHERE customer_id = 10 AND IFNULL(request_status,"other") NOT LIKE "friends"

SQL LEFT JOIN for all records of first table and matched records of another table

2 Answers2