[Dbix-class] selecting records with no links-to

[Ronald Kimball]

On Fri, Jun 14, 2013 at 7:31 AM, Dave Howorth <dhoworth at mrc-lmb.cam.ac.uk>w=
rote:

> My brain is feeling fried and is failing to solve a simple puzzle.
>
> Using the standard table 'artist' which has a primary key 'artistid'.
>
> And the other table 'cd' that has a link to artists, via a foreign key
> 'artist'. There may be more than one CD associated with a single artistid.
>
> I want a resultset containing all artists which have NO associated CD.
>
> What's the appropriate way to select this resultset?
>

I think you'd want to do a left outer join where cd's primary key is null.

SELECT *
FROM artist
LEFT OUTER JOIN cd
ON artist.artistid =3D cd.artist
WHERE cd.cdid IS NULL

-- =

*Ronald J Kimball*
Senior Developer
 - www.snapapp.com | Facebook <http://www.facebook.com/snapapp> *|*
Twitter<http://www.twitter.com/snap_app>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130614/a3c=
d2fb5/attachment.htm