best mysql query for selecting multiple rows that each need multiple associative rows from several child tables

Whew, the title is a m开发者_JAVA技巧outhful. Once again I find myself not knowing exactly how to ask this question so I will use an example. Let's say you're making a game that has items. Items have effects, bonuses and requirements.

So each record in the items table has multiple child records in the effects table, the bonuses table and the requirements table.

And you want to select, let's say, the 100 most recent items, including all of their effects, bonuses and requirements to display in the game.

What is the most optimized way to accomplish this. Can it be done in one query? And is that even practical? Thanks.

It could be achieved in one query, but it would be quite large and there would be a lot of doubling up. The only really ideal time doing something like this in one query is if there is a "has one" relationship. i.e. An item has one effect, which can be done with a simple join, and an item only returns one show.

Take a simple case of what you've given. You have 2 items, each with 3 effects with a many to many relationship.

SELECT item.*, effect.* 
FROM item 
JOIN item_effect ON item.id = item_effect.item_id 
JOIN effect ON effect.id = item_effect.effect_id

The return could be:

item1   effect1
item1   effect2
item1   effect3
item2   effect2
item2   effect3
item2   effect4

Then you would have to loop through and group all of the items back together. With relationships to requirements and modifiers the query would be getting larger, but still fairly organized.

You could use some type of ORM (Object Relational Mapping). Which could make your code more readable, e.g. Using syntax from Kohana's ORM.

$items = ORM::factory('item')->find_all();
foreach($items as $item) {
    $effects = $item->effects->find_all();
    $bonuses = $item->bonuses->find_all();
    $requirements = $item->requirement->find_all();
}

But for the 100 item example you suggested that will be 301 queries.

If you are displaying this on a web page, then pagination (showing 1-20 of 100) will lower that number.

The method you use really depends on your situation. Things to consider:

• How often will this be used
• Do they really need to see 100 items at once
• Do they need to see all the relationships at one (click an item to view its effects, etc.)

You should be able todo something like this...

 SELECT  `users`.`nickname` ,  `events`.`nickname` 
 FROM  `users` ,  `events` 
 WHERE  `events`.`aid` =  `users`.`aid` 
 GROUP BY  `events`.`nickname` 

To clarify, events.aid is the unique ID of the user. So when I fetch all these records and group them by event, I get a list of all unique event nicknames and the users that created them.