CODEWITHSUNDEEP
mysqlsql
Wojciech Szabowicz
Wojciech Szabowicz

Reputation: 4198

Get empty records from 3 tables

I have got 3 tables that looks like

table 1 columns: id, product

table 2 columns: id, table_1_id model

table 3 columns: id, table_2_id, salesman

As can be seen, table one is connected to table two by one to many, and also table two is connected to table three by one to many. I need to get products that don't have any salesman. I've tried to check if the model salesman is null, but if one model have salesman and other don't of the same product, it still shows up in results.

Upvotes: 0

Views: 57

Answers (2)

Kickstart
Kickstart

Reputation: 21513

If I understand your requirements, think a COUNT with a check for a result if 0 will do it:-

SELECT a.id, a.product, COUNT(c.id) AS salesman_count
FROM table_1 a
LEFT OUTER JOIN table_2 b ON a.id = b.table_1_id
LEFT OUTER JOIN table_3 c ON b.id = c.table_2_id
GROUP BY a.id, a.product
HAVING salesman_count = 0

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116110

This will return products that don't have any salesman for any of their model. This also returns products that don't have models.

select
  p.id, p.product
from
  table1 p
where
  p.id not in (
    select 
      m.table_1_id
    from
      table2 m
      inner join table3 s on s.table_2_id = m.id)

Instead of (not) in, you can also use (not) exists. Below I use both, to return only products that do have a model, but don't have a salesman for it.

select
  p.id, p.product
from
  table1 p
where
  exists (
    select 'x' 
    from table2 m 
    where m.table_1_id = p.id) and
  p.id not in (
    select 
      m.table_1_id
    from
      table2 m
      inner join table3 s on s.table_2_id = m.id)

Alternatively, you may want to show the models as well:

select
  p.id as productid, 
  p.product,
  m.id as modelid,
  m.model
from
  table1 p
  inner join table2 m on m.table_1_id = p.id
where
  not exists (
    select 'x' 
    from table3 s 
    where s.table_2_id = m.id)

Instead of exists, sometimes a left join is used/abused. I think it's less readable, but sometimes it is faster (can be slower as well!). 

select
  p.id as productid, 
  p.product,
  m.id as modelid,
  m.model
from
  table1 p
  inner join table2 m on m.table_1_id = p.id
  left join table3 s on s.table_2_id = m.id
where
  s.id is null

Upvotes: 2

Related Questions