I tend to find that "exists" is very helpful for these situation (and reads
like the english version of what I want).
SELECT artistid,name
FROM artist
WHERE EXISTS (
SELECT artistid
FROM track
INNER JOIN artisttrack
ON track.trackid = artisttrack.trackid
WHERE track.albumid = 2
AND artisttrack.artistid = artist.artistid
)
This says "Give me all of the artists that perform on a track for on album 2."
You could change the "2" to any value, of course.
Of course, I tend not to worry about performance unless testing shows it to be
an issue.
Steve Bryant
918-449-9440
Bryant Web Consulting LLC
http://www.BryantWebConsulting.com/
http://steve.coldfusionjournal.com/
>There can multiple artists per track (e.g. the album "Back to Back" by
>Duke Ellington and Johnny Hodges). For now, forget about albums that
>might have different artist lineups per track. I'd like a listing that
>shows _all_ of the artists on an album. Can this be done in a single
>query, and in SQL (MySQL) only, without any manipulation by CF after the
>query?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259264
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
