[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