[thelist] SQL replace subquery with join

[Bill Moseley]

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