Maarten Martens
June 14th, 2003, 18:53
Hi,
I have an MYSQL question. I don't know if this is possible. let me first give my db structure regarding this :
pub_dev
pub_dev_id
pub_dev_name
This is the main table which holds all companies
developer
pub_dev_id
games_id
publisher
pub_dev_id
games_id
now what I like to do is select all games starting with a given letter, and get the publisher and developer of this game, and the year of release. I would like to do this in one statement, the problem now is that the pub_dev table has to be contacted twice. Once to get the publishers name, and once you get the developers name. And ofcourse, the second time we use this table, the id is overwritten. This is the query I use :
SELECT * FROM game
LEFT JOIN publisher ON (game.game_id = publisher.game_id)
LEFT JOIN pub_dev as st1 ON ( publisher.pub_dev_id = st1.pub_dev_id )
LEFT JOIN developer ON (game.game_id = developer.game_id)
LEFT JOIN pub_dev as st2 ON ( developer.pub_dev_id = st2.pub_dev_id )
LEFT JOIN game_year ON (game.game_id = game_year.game_id)
WHERE game.game_name LIKE 'a%' ORDER BY game.game_name ASC LIMIT 0, 30
My question, is there some way to get this fixed? Is there a way to call pub_dev with the publishers id and call it with the developers id, and yet get both their data back?
Thanks,
Maarten
I have an MYSQL question. I don't know if this is possible. let me first give my db structure regarding this :
pub_dev
pub_dev_id
pub_dev_name
This is the main table which holds all companies
developer
pub_dev_id
games_id
publisher
pub_dev_id
games_id
now what I like to do is select all games starting with a given letter, and get the publisher and developer of this game, and the year of release. I would like to do this in one statement, the problem now is that the pub_dev table has to be contacted twice. Once to get the publishers name, and once you get the developers name. And ofcourse, the second time we use this table, the id is overwritten. This is the query I use :
SELECT * FROM game
LEFT JOIN publisher ON (game.game_id = publisher.game_id)
LEFT JOIN pub_dev as st1 ON ( publisher.pub_dev_id = st1.pub_dev_id )
LEFT JOIN developer ON (game.game_id = developer.game_id)
LEFT JOIN pub_dev as st2 ON ( developer.pub_dev_id = st2.pub_dev_id )
LEFT JOIN game_year ON (game.game_id = game_year.game_id)
WHERE game.game_name LIKE 'a%' ORDER BY game.game_name ASC LIMIT 0, 30
My question, is there some way to get this fixed? Is there a way to call pub_dev with the publishers id and call it with the developers id, and yet get both their data back?
Thanks,
Maarten