Need guidance on SQL query

Question

So the problem is:

fan(fanID: integer, fanName: string)
band(bandID: integer, bandName: string)
likes(fanID: integer, bandID: integer)
bandMember(memID: integer, memName: string, bandID: integer, nationality: string)

(a) List the names of the bands that have only British members.

At first I thought I came up with this solution:

Select bandname
from band
where band.id NOT IN
    (select bandMember.id
     from bandMember
     where nationality <> 'British')

BUT then I was thinking how I was only selecting a band with AT LEAST one british member. It doesn't necessarily mean that everyone is British. Can someone help think of a way to query to find all the members of a certain band to check if they're all british??

What do the `fan` and `likes` tables have to do with this question? Did you meant `select bandMember.bandID` in the query above? — Matt Ball, Jul 16 '13 at 22:32

Bill Karwin · Answer 1 · 2013-07-16T22:55:51.277

Join the band to its members, but only for non-British members. Do this with an OUTER JOIN and it's important to specify the nationality in the join condition. Then if there is no match, the outer join will return all columns of bandMember as null.

SELECT b.bandname
FROM band b
LEFT OUTER JOIN bandMember m
 ON b.bandID = m.bandID AND m.nationality <> 'British'
WHERE m.bandID IS NULL

Re your comment:

The above query should be equivalent to the following query:

SELECT b.bandname
FROM band b
WHERE b.bandID NOT IN 
  (SELECT bandID FROM bandMember WHERE nationality <> 'British')

That's slightly different from your original idea, but I'm comparing bandId to bandId.

But you should really get used to using JOIN in SQL. Trying to program in SQL without JOIN is like trying to program in most other languages without while. Not only is it a fundamental part of the SQL language, but it is better for performance in many cases. YMMV; test both solutions against your data.

Thank you very much for your help. is there any way to do this without the use of ON? Thanks again. — david, Jul 16 '13 at 22:47

score 0 · Answer 2 · answered Jul 16 '13 at 23:28

Here is a solution that uses group by. I prefer using group by and having for "set-within-a-set" subqueries:

select b.bandname
from band b join
     bandmember bm
     on b.id = bm.bandid
group by b.bandname
having sum(case when bm.nationality <> 'British' then 1 else 0 end) = 0;

What is nice about this solution is that it adapts very easily. Say you want a band with at least two British members:

having sum(case when bm.nationality = 'British' then 1 else 0 end) >= 2;

By the way, your query should not work at all because you are comparing band.id to bandMember.id. The inner select should be bandId.

Need guidance on SQL query

2 Answers2