relational query woes

Eep__

I want to have the contents of one table's field appear in another table's field but I can't seem to make it work with:

SELECT
compare.id,
games.game,
genres.genre,
compare.publisherID,
compare.engineID,
compare.developerID,
users.user
FROM
compare,games,genres,users
WHERE
compare.gameID=games.gameID
AND
compare.userID=users.userID
AND
genres.genreID=compare.genreID

genreID keeps coming out to "3" instead of "1,2" as it is in compare.genreID which I want translated to "action" and "adventure" using the names in the "genres" table via genres.genreID.

Is there a way to have MySQL do this automatically or must I use PHP to convert compare.genresID ("1,2") to genres.genre ("action,adventure")?

irishprogrammin

you need to take care of it, mysql doesn't support it, at least from what i have read.

Eep__

Take care of it how? Please give me specific details. Thanks.

I mean what's the point of relational tables if MySQL can't handle the relationships automatically? Why shouldn't I just put the genre names in the "compare" table?

trooper

Without seeing a schema of you tables i can't be 100% sure but i think your query is slightly wrong.

Paste you schema here so we can take a look at the fields in the tables

Originally posted by Eep²
Take care of it how? Please give me specific details. Thanks.

I mean what's the point of relational tables if MySQL can't handle the relationships automatically? Why shouldn't I just put the genre names in the "compare" table?

Eep__

See http://www.phpbuilder.com/board/showthread.php?s=&postid=10231349 for the relevant table setup and data.