MePo MePo - 20 days ago 4
MySQL Question

Get count where relation (one-to-many) doesnt exists in same table

I am trying to get Number of records which dont have relations (one-to-many) but the problem is it is in the same table and i am not sure how to do this.

| id | person_id | name |
| 1 | NULL | John Doe |
| 2 | NULL | Jane Doe |
| 3 | 1 | Junior J.D.|
| 4 | 1 | Senior J.D.|

Now from this, for example i would like to get
count = 1
since Jane Doe doesnt have a relationship (doesnt have one-to-many).

For better explanation i need (from example) people who are "Single", as "John Doe" has child "Junior J.D." so he doesnt count.

I know how to get count of people who dont have relation (using Laravel Raw)

$query = DB::table('people')
->select(DB::raw('count(*) as count'))
->where('person_id', '=', NULL)

For now i figured i would use Exists but i am not sure how to implement that (or i might be wrong).


You can use whereNull method to get only rows that have column that have NULL value.


Edit: After your edit, I think i understood the question.

This SQL query should work for you:

SELECT COUNT(*) as count
FROM people
WHERE person_id IS NULL
    AND id NOT IN (
         SELECT person_id 
         FROM people 
         WHERE person_id IS NOT NULL

If we translate this to Laravel:

$query = DB::table('people')
             ->whereIn('id', function($query) {
             }, 'and', TRUE)

Third argument to whereInSub is for boolean logic to combine query. And fourth argument means we want NOT IN.

This is not tested. So please test it and let me know if it works.

Also this is not the best way to do it i guess. If its possible I suggest to change your database structure.