So far I've made multiple statements pulling out all the data and using PHP
to formulate the information. But I know there is a better way using mysql.
Only thing is I haven't got the faintest idea how to do it in one query and
keep overheads low. I start to get lost when I'm doing select statements
within select statements.
I'm no DB guru, but as I understand it what you want to do requires the use of foreign keys. InnoDB tables implement them, but I don't think they currently grab the related information when you do SELECT statements. It sounds like you are emulating that right now with your SQL statements, and I don't think that any of the 4.x versions of MySQL will fully support foreign keys. I did find something interesting, though: apparently MySQL is going to support *stored_procedures* and *foreign_keys* in the 5.0-5.1 branch. I may have to go play with some of the 5.0 alpha stuff soon...
http://dev.mysql.com/doc/mysql/en/TODO.html