-1

I have the following tables :

Employee (empid,name,age)
Booking (bookingid,empid,cid)
Cars (cid,name,type).
The contents are in this way:

Employee :

  1. emp1 , A , 32
  2. emp2 , B , 36
  3. emp3 , C , 25
  4. emp4 , D , 40
  5. emp5 , E , 45

Booking:

  1. 1 , emp1, car1
  2. 2 , emp1 , car3
  3. 3 , emp1 , car2
  4. 4 , emp2 , car2
  5. 5 , emp3 , car3
  6. 6 , emp4 , car1
  7. 7 , emp4 , car2
  8. 8 , emp5 , car3
  9. 9 , emp5 , car2
  10. 10 , emp5 , car1

Cars:

  1. car1 , one, Red
  2. car2 , two, Blue
  3. car3 , three, Red

Here's my question : what is the query to return all employee names who have booked a car which isnt red?

Edited :
I should've been more specific with my question the first time.
This link How can I join two tables but only return rows that don't match?
It is about comparing same fields from different tables.The post clearly indicates that the user needs to compare rows from two tables and return ones that don't match.
But that is not my case.As my title suggests,I'd like to know if the rows returned by performing a query can be compared.
I want to get the names of employees who have not booked at least one red car. Considering the values in the tables,
Employee 1 and 5 have booked all cars.
Employee 3 has booked only a red car and
Employee 4 has booked one red and one green car.
Employee 2 has booked a Green car.
Now, what can be the query to return only name of employee 2 is what my question is.(I'm know i can do it by just specifying type='Green' in the where clause but that fails when there are more than two colors.)

rcb_sp
  • 65
  • 5
  • 3
    You should attempt to solve this problem before asking on the web. If you can't solve this one, you might try to get all employees who *have* booked a red car and see if that gives you any ideas. – Gordon Linoff Feb 29 '16 at 19:23
  • 2
    Welcome to SO. Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! Also see [ask]. Remember **SO is not a free code service**! – Jorge Campos Feb 29 '16 at 19:26
  • @GordonLinoff Thank you for the suggestion. But I've tried many queries but i ended up with the same result which still doesn't give me names of all people who have not booked a red car. I'd like to know how exactly that is possible. If there is anything similar to what I've asked, I'd be glad to check it out :) – rcb_sp Feb 29 '16 at 21:04

1 Answers1

1
select DISTINCT e.Name from #Employee e
inner join #Booking b on e.EmpID = b.EmpID
where e.EmpID not in (
    select b.EmpID from #Booking b
    inner join #Cars c on b.CID = c.CID
    where [type] = 'red'
)
Roberto
  • 533
  • 2
  • 10
  • But this gives me all the employee names who have reserved a car other than a red one i.e. that if employee 1 has reserved a red car and a green car , it would still return me the name of employee 1 because he has reserved a green car . i'm looking for a query which ignores the employee if he reserved at least one red car . – rcb_sp Feb 29 '16 at 20:36
  • thanks for taking the patience to edit your answer. But what if there are employees who have not booked a car at all. The above query returns their names as well . – rcb_sp Feb 29 '16 at 21:10
  • finally :D .. thank you for the answer :)) – rcb_sp Feb 29 '16 at 21:33