Query Trouble

[Shephard]

Hi,

I'm new to mySQL and I've got to run a bunch of queries on a database that I've been designing for a hypothetical record company. If anyone could check my queries, I would appreciate it greatly as they don't seem to be returning the correct results and I'm not sure if it is something in the query or something in the way the database is set up.

Thanks in advance

Ok, so I have is to list all the Musician's names and SSN's and the Album's name who have produced an Album with a particular song on.
The Album has the following attributes: an ID, title, song ID (acting as foreign key to a Song) and the musican SSN (acting as foreign key to Musician)

The query I wrote was:

select ATITLE, NAME, SSN
from Album, Musician 
where SONGID = 
(Select SONGIDENTIFIER
from Song
where STITLE = "Sketches of Manchester")

However the result came out with every Musician in the database, with the two albums on which the song appears next to each.

Hope that sufficiently explains the problem, please message me if more information is needed.

Again thanks for any help

Shephard

[smantscheff]

How can an album have exactly one SongID? There are more than one songs on an album. Seems to be a database design issue.
Do a mysqldump of your database and post it here.

[Shephard]

Sorry this was part of the spec given, every album can have multiple songs on it, but every song can only appear on one album. However I have been looking at my relational schema and I believe that the SongID is the primary key for the Album table.

Not sure if that helps clear things up.

Thanks

[smantscheff]

Please post the schema.