CODEWITHSUNDEEP
mysql
Joshua Ohana
Joshua Ohana

Reputation: 6121

Left outer join, find all from left having no occurrences in right table, mysql

Having two tables, a mapping and a data table, I want to find all mappings that don't have any associated data

For example:

     map                 data
============        ===============
mapId | name         mapId | value
------------        ---------------
  1      A             1       x
  2      B             1       y
  3      C             2       z

For this case I want to return the mapId 3/C, since it exists in the map table but does not have a record in the data table

How can I query this? I've tried every combo of group and having and where I can think of, the closest I got was

SELECT map.name
FROM map
LEFT OUTER JOIN data on data.mapId = map.mapId
GROUP BY data.mapId
HAVING max(data.value) is null

I also tried grouping by map.mapId, and having count(data.dataId) = 0, all to no avail. NO matter how I set it up I'm getting either some maps that do have data, or not getting some maps that don't have data.

Upvotes: 0

Views: 249

Answers (2)

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

You don't need to group by to achieve this. There can be multiple rows for one mapId in data do I added distinct.

select
    distinct m.mapId, m.name
from map m
left join data d
    on m.mapId = d.mapId
where d.mapId is null

Upvotes: 1

forpas
forpas

Reputation: 164064

No need to group, a left join is enough:

SELECT map.*
FROM map LEFT JOIN data 
on data.mapId = map.mapId
WHERE data.mapId is null

Also another way to do it with NOT EXISTS:

SELECT map.*
FROM map  
WHERE NOT EXISTS (
  SELECT 1 FROM data 
  WHERE data.mapId = map.mapId
)

Upvotes: 1

Related Questions