mysqlsqlduplicates

MySQL column value not exist in other column on the same table

I want to run a mysql query to select all rows from a table "a" where the value of the ID column does not exist in ID_REFERENCE column.

Here is a my table content:

| ID | ID_REFERENCE | TOTAL | NAME |
| 1  |     3     |   1500  |  John  |
| 2  |     -      |   1000  |  Eric   |
| 3  |     1     |   1300  |  John  |
| 4  |     8     |   1000  |  Alex  |
| 5  |     -      |   2000  |  Bani |
| 6  |     7     |   1800  |  Adis  |
| 7  |     -      |     500  |  Adis  |
| 8  |     4     |     700  |  Alex  |

i need to display to this

| ID | ID_REFERENCE | TOTAL | NAME |
| 1  |     3     |   2800  |  John  |
| 2  |     -      |   1000  |  Eric   |
| 4  |     8     |   1700  |  Alex  |
| 5  |     -      |   2000  |  Bani |
| 6  |     7     |   2300  |  Adis  |

ID column is unique

i've had trouble getting the hoped. Thanks

Solution

  • use not exists

     select t1.* from a t1 where
 not exists ( select 1 from a t2 where t2. ID_REFERENCE =t1.id)

    or use not in

     select t1.* from a t1 where a.ID not in ( select  ID_REFERENCE  from a where a. ID_REFERENCE  is not null)