Topic: Querying an association table

[lemmin]

I have a table with items that have related qualities with a many-to-many relationship.

Code: [Select]

ItemsTable
   ItemID
   ItemInfo

ItemQualitiesTable
   ItemID
   QualityID

In this situation, I can either query for the item information first and then make a second query to get the related qualities. OR I could use a LEFT JOIN to include the qualities in one single query.

The problem with the first method is that I would have to make two separate queries which is inefficient. The problem with the second method is that it would return a copy of ALL of the information for every different quality that exists for each item, which is inefficient.


Is there a way around these inefficiencies? If not, what is the best method of retrieving the needed information? The first method might execute faster, but it would require a TON more memory and vise-versa for the second method.

Maybe there is even a better way to structure these tables to make the queries more efficient?

Thanks for any help.

[fenway]

Then use a derived table for the second method.

[JustLikeIcarus]

If you want the result to look like ItemID, ItemInfo, QualityID, QualityID, etc... with all of the Quality ID's on one row take a look at using mysql's GROUP_CONCAT() function http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat . It handles this very well.