0 Replies - 281 Views - Last Post: 07 February 2011 - 05:19 PM

#1 bern1812  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 18-March 10

"Every" SQL Query Help

Posted 07 February 2011 - 05:19 PM

I am having some trouble figuring out how to do an "every" query, which i believe would be universal quantification.

My database schema looks like this:

CAR(Serial_no, Model, Manufacturer, Price)
OPTIONS(Serial_no, Option_name, Price)
SALE(Salesperson_id, Serial_no, Date, Sale_price)
SALESPERSON(Salesperson_id, Name, Phone)

What I am trying to find is the names of the salesmen who have sold a car from every manufacturer.

I have an example from a different database for a similar "every" query with the schema:

EMPLOYEE(id, age, salary)
PROJECT(id, name);
WORKS_ON(empId, projId, startDate)

To find employees that have worked on all projects I would use the query:
Select * 
from EMPLOYEE E
WHERE not exists (
select *
from PROJECT P
where P.id not in (
select W.projId
from WORKS_ON W
where E.id = W.empID));



This is what I am trying to use as my base for the query I am trying to do.
This is what I have, but it is coming up empty, where it should be giving me at least 1 person:

 select * 
 from SALESPERSON P 
 where not exists ( 
 select *  
 from CAR C 
 where C.Serial_no not in (
 select S.Serial_no 
 from SALE S 
 where P.Salesperson_id = S.Salesperson_id));



Could anyone point me in the right direction on this query, and maybe some tips for queries like it, as these always tend to give me trouble? Any resources/tutorials/webbooks for these kinds of SQL queries would be much appreciated.

Is This A Good Question/Topic? 0
  • +

Page 1 of 1