I'm not sure why you split out track, track is really kind of an attribute of a song. Especially since you have artist and album
with the song. Wouldn't album be the same as cd title? I'm not quite getting the logic of your schema.
It would have been helpful if you provided your current query.
Anyway, you want to find out what CDs contain a particular song. So start your query out by finding the song, then you need to find
what tracks it's associated with, then what cd those tracks are associate with.
Something like this should work:
SELECT cd.title,song.title,track
FROM song
JOIN track ON song.id=song_id AND song.id='X'
JOIN cd ON track.cd_id=cd.id
Same query, worded slightly different:
SELECT cd.title,song.title,track
FROM song
JOIN track ON song.id=song_id
JOIN cd ON track.cd_id=cd.id
WHERE song.id='X'
----- Original Message -----
From: "Tanner Postert" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, August 01, 2006 8:21 PM
Subject: Is this query possible?
ok, here is the schema that I am working with:
CREATE TABLE `cd` (
`id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned NOT NULL,
`title` varchar(100) NOT NULL,
`description` text NOT NULL,
`dt` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM
CREATE TABLE `song` (
`id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned NOT NULL,
`title` varchar(50) NOT NULL,
`artist` varchar(50) NULL,
`album` varchar(50) NULL,
`featuring` varchar(50) NULL,
`length` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM
CREATE TABLE `track` (
`id` int(10) unsigned NOT NULL auto_increment,
`song_id` int(10) unsigned NOT NULL,
`cd_id` int(10) unsigned NOT NULL,
`track` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `cd_id` (`cd_id`,`track`),
UNIQUE KEY `song_id` (`song_id`,`cd_id`)
) ENGINE=MyISAM
the query now, is pulling back all the cd table data, as well as the total
length of the songs that are tracks on that CD, as well as the number of
tracks. that query is working fine.
my goal now, is to pull the same data, but only for CDs that contain a
specific track. but if i add "where song.id = 'X' the the count only returns
1 and the sum only returns the length for that 1 song. I'd like the full
length and track count, but only for CDs that contain a specific song.
SELECT cd.*,
count(track.track) as tracks,
sum(song.length) as length
from cd LEFT JOIN (track, song) on
(track.cd_id = cd.id
and track.song_id = song.id)
GROUP BY cd.id
ORDER BY dt DESC
LIMIT 0,1
thanks,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
