SQL: Have 4 Tables, Looking to find unmatched data.

Question

I've always done this back asswards in PHP or ASAP, so I figure it's time to actually learn the proper way to do it in SQL. I have the following 4 tables in a database:

Category (Fields: CategoryNumber, Desc) (small table with 15 rows)

Media (Fields: MediaID, Desc, CategoryNumber, etc) (huge table with 15,000 rows)

Sales (Fields: Date, MediaID, EmployeeID etc) (huge table with 100,000 rows)

Employees (Fields: EmployeeID, Name, etc) (small table with only 20 rows)

Category only links to Media Media has links to both Category and Sales. Sales links to both the Media and Employee Employee only links to Sales

What I would like to do is to write a query which tells me what categories a given employee has never sold any media in.

I can write a simple query that looks for unmatched data between 2 tables, but I have no clue how to do it when I'm dealing with 4 tables.

Thanks for your time and help!

Answer 1

SELECT c.CategoryNumber, c.Desc
FROM   Category c
WHERE NOT EXISTS
(
SELECT *
FROM       Employees e
INNER JOIN Sales     s on s.EmployeeID = e.EmployeeID
INNER JOIN Media     m on m.MediaID    = s.MediaID
WHERE e.Name = "Ryan"
AND   m.CategoryNumber = c.CategoryNumber
)

MS Access evidently needs a lot of parentheses (thanks, Ryan!):

select * 
from Category c 
where not exists 
( select * 
from (     Employee e 
inner join Sales    s on (s.EmployeeId = e.EmployeeId)) 
inner join Media    m on (m.MediaID    = s.MediaID) 
where (e.Name = 'Ryan' and m.CategoryNumber = c.CategoryNumber) )

Answer 2

Here's my suggestion:

select *
from Category c
where not exists (
    select *
    from Employee e
    inner join Sales s on s.EmployeeId = e.EmployeeId
    inner join Media m on m.MediaID = s.MediaID
    where e.Name = 'Ryan' and m.CategoryNumber = c.CategoryNumber
)

To query all employes with the categories in which they didn't sell anything:

select e.EmployeeName, c.CategoryNumber
from Category c
cross join Employee e
where not exists (
    select *
    from Sales s
    inner join Media m on m.MediaID = s.MediaID
    where c.categoryNumber = m.CategoryNumber
    and s.EmployeeId = e.EmployeeId
)

Answer 3

select c.desc
from category
left outer join (select s.employeeid,m.categorynumber
      from sales s 
      inner join media m on s.mediaid=m.mediaid
      inner join employee e on e.employeeid=s.employeeid
      where e.name = 'JOE'
      group by employeeid,categorynumber) t on t.categorynumber=c.categorynumber
where s.employeeid is null