Select values which are not present in other table in MYSQL

Question

I want to be able to pick peoples in table 1 that did not gave or asked ID#1(jud) for loans. MY 2 tables.

table 1
----------------------
id*|name|adress
1  |jud |pipi #5
2  |john|yoba #45
3  |jan |stackyflow 54
4  |Song|doesnt matter street 98

Table 2
-------------
*id|asked_loan|gave_loan
1  |1         |2
2  |1         |3

In this case i will want to get only Table1 row #4 which is :

4  |Song|doesnt matter street 98

What i tried :

SELECT DISTINCT `id`,`name`,`adress`
FROM `TABLE1`
LEFT JOIN `TABLE2` ON TABLE1.asked_loan = TABLE1.id
WHERE `asked_loan` !=1 AND `gave_loan` !=1

My problem: Since i am left joining, the 2 tables hook together so users not present in TABLE2 will not show up.

Summary: I want to be able to select users that did not gave or receive (TABLE1 id)#1 a loan

Does this answer your question? [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — philipxy, Feb 24 '20 at 17:30
Please give a [mre]. PS In the duplicate the table that has no matching value goes 2nd. — philipxy, Feb 25 '20 at 16:04

ScaisEdge · Answer 1 · 2020-02-24T16:22:37.523

0

You should select the rows that are not matching:

    SELECT DISTINCT  TABLE1.id,`name`,`adress` 
    FROM `TABLE1` 
    LEFT JOIN `TABLE2` ON TABLE2.asked_loan=TABLE1.id  
     AND  `asked_loan` !=1 
      AND `gave_loan` !=1
    WHERE  TABLE2.asked_loan is null

edited Feb 24 '20 at 16:22

answered Feb 24 '20 at 16:08

ScaisEdge

131,976
10
91
107

I tried this but i get an error,should i add a sign after TABLE2.asked_loan=TABLE1.id ? – Goldberg Feb 24 '20 at 16:20
This works when there is only 1 record of everything but if there is more records it wont work. for example if table 2 has row of (4)(2). it will show up in the results – Goldberg Feb 24 '20 at 16:43

score 0 · Answer 2 · answered Feb 24 '20 at 16:38

0

One option would be using NOT EXISTS through

SELECT *
  FROM table1 t1
 WHERE NOT EXISTS 
      (SELECT 0 
         FROM table2 t2 
        WHERE t1.ID IN (t2.asked_loan,t2.gave_loan) )

matching those two columns of table2 with ID column of table1

Demo

answered Feb 24 '20 at 16:38

Barbaros Özhan

59,113
10
31
55

Its a great step but this is not really what i want, this gives the users that never gave or received a loan from nobody. I want to get the users who never gave or took a loan from only ID 4. – Goldberg Feb 25 '20 at 15:43

Select values which are not present in other table in MYSQL

2 Answers2