[thelist] SQL replace subquery with join

David Kaufman david at gigawatt.com
Tue Jan 5 12:21:12 CST 2010


Hi Bill,

Bill Moseley wrote:
> Ok, I'm ready for a "doh!" moment.
> 
> I have a database to manage my CD collection (yes, a bit dated example).
> 
> Two tables, one is the cd table with (id, name) and another table genre with
> (cd, name) where name might be "rock" or "jazz".
> For the sake of this question a cd can have many genres (zero or more).
> 
> Say I want a list of CDs that do NOT have a genre of "rock".
> 
> I could use a sub-query [but]
> I know there's a way to use a join for this, though.
> 
> SELECT cd.*
> FROM
>     cd
>     LEFT JOIN genre ON genre.cd = cd.id
> WHERE
>     (genre.name IS NULL or genre.name != 'rock')
> 
> But, of course, I get CDs I do not want.  That is if a cd has rock, jazz,
> and alternative genres associated then I'll still get the jazz and
> alternative rows.
> 
> I want distinct CDs and only ones that don't have "rock" as their genre.
> 


You could group by an aggregate function, and use a HAVING clause.

In mysql, for instance, the IF() function can be used to add a numeric 
"isrock" column to the result set:

   SELECT *,
     IF(genre.name = 'rock', 1, 0) AS isrock
   FROM cd
     LEFT JOIN genre ON genre.cd = cd.id;

     +----+----------------+------+---------+--------+
     | id | artist         | cd   | name    | isrock |
     +----+----------------+------+---------+--------+
     |  1 | led zeppelin   |    1 | rock    |      1 |
     |  2 | lynyrd skynyrd |    2 | country |      0 |
     |  2 | lynyrd skynyrd |    2 | rock    |      1 |
     |  3 | dead kennedys  |    3 | punk    |      0 |
     |  3 | dead kennedys  |    3 | 80s     |      0 |
     |  4 | garth brooks   |    4 | country |      0 |
     |  5 | bach           | NULL | NULL    |      0 |
     +----+----------------+------+---------+--------+

Then, grouping by cd.id, we can use the aggregate function MAX() to pare 
that down to a unique list of cd's, including, whether or not they have 
rock in their genre list:

   SELECT cd.*,
     MAX(IF(name='rock', 1, 0)) AS isrock
   FROM cd
     LEFT JOIN genre ON genre.cd = cd.id
   GROUP BY cd.id;

     +----+----------------+--------+
     | id | artist         | isrock |
     +----+----------------+--------+
     |  1 | led zeppelin   |      1 |
     |  2 | lynyrd skynyrd |      1 |
     |  3 | dead kennedys  |      0 |
     |  4 | garth brooks   |      0 |
     |  5 | bach           |      0 |
     +----+----------------+--------+

Then just filter out the groups HAVING isrock = 0:

   SELECT cd.*,
     MAX(IF(name='rock', 1, 0)) AS isrock
   FROM cd
     LEFT JOIN genre ON genre.cd = cd.id
   GROUP BY cd.id
   HAVING isrock=0;

     +----+---------------+--------+
     | id | artist        | isrock |
     +----+---------------+--------+
     |  3 | dead kennedys |      0 |
     |  4 | garth brooks  |      0 |
     |  5 | bach          |      0 |
     +----+---------------+--------+


Hope this helps!

-dave


More information about the thelist mailing list