[thelist] SQL replace subquery with join
Bill Moseley
moseley at hank.org
Tue Jan 5 13:33:22 CST 2010
On Tue, Jan 5, 2010 at 9:15 AM, r937 <rudy at r937.com> wrote:
> hi bill
>
> try like this --
> SELECT cd.*
> FROM cd
> LEFT OUTER
>
> JOIN genre
> ON genre.cd = cd.id
> AND genre.name = 'rock'
>
> WHERE genre.name IS NULL
>
> in the ON clause of the outer join, put what you're looking for (i.e.
> looking for rock)
>
> then the WHERE clause filters those out, leaving only the ones that aren't
> rock
>
Yes, that's probably best. Again, I'm fighting a bit with an ORM which
will do joins but does not (well not cleanly) allow for additional
conditions in the JOIN. I actually started out with that approach but when
doing that the ORM is unable to do other things (like fetching multiple
related objects in a single query).
It's interesting that you pointed that out, though, as I have another join
that was almost exactly like that.
For example, I have an associated table "plays" where a row is added every
time the CD is played. That table also has a "device" column that indicates
the device that played the CD.
Say I want a list of CDs and a count of how many times they were played on
device #1.
I think these return the same rows, but as you pointed out in your later
email, the second query probably selects a lot more rows just to end up
throwing them away.
SELECT
cd.id,
count( p.id )
FROM
cd cd
LEFT JOIN plays p on p.cd = cd.id AND device = 1
GROUP BY
cd.id
SELECT
cd.id,
count( p.id )
FROM
cd cd
LEFT JOIN plays p on p.cd = cd.id
WHERE
( device IS NULL OR device = 1 )
GROUP BY
cd.id
Thanks for all the help. The ORMs are a mixed blessing.
--
Bill Moseley
moseley at hank.org
More information about the thelist
mailing list