Let's say I have the following tables: Plates table +----+------------+-------------+ | id | Name | Description | +----+------------+-------------+ | 1 | Paper | Blah | | 2 | Plastic | Blah | | 3 | China | Blah | | 4 | Glass | Blah | +----+------------+-------------+
Cups table +----+------------+-------------+ | id | Type | Description | +----+------------+-------------+ | 1 | Paper | Blah | | 2 | Mug | Blah | | 3 | Coffee | Blah | | 4 | Glass | Blah | +----+------------+-------------+ Flatware table +----+------------+-------------+ | id | Form | Description | +----+------------+-------------+ | 1 | Spork | Blah | | 2 | Plastic | Blah | | 3 | Antique | Blah | | 4 | Tin | Blah | +----+------------+-------------+ Inventory table +----+------------+--------+-------+ | id | ItemType | ItemId | Owned | +----+------------+--------+-------+ | 1 | PLATES | 2 | 17 | | 2 | CUPS | 4 | 3 | | 3 | FLATWARE | 3 | 6 | | 4 | CUPS | 3 | 9 | | 5 | CUPS | 1 | 7 | | 6 | FLATWARE | 4 | 12 | | 7 | PLATES | 1 | 1 | +----+------------+--------+-------+ Is there a way to construct a query so that only the appropriate tables are included as a join? I'm trying to do a conditional (and more elegant) version of the following query: SELECT Inventory.id, CASE Inventory.ItemType WHEN 'PLATES' THEN Plates.Name WHEN 'CUPS' THEN Cups.Type WHEN 'FLATWARE' THEN Flatware.Form END as ItemName Inventory.ItemType, Inventory.ItemId, Inventory.Owned FROM Inventory LEFT OUTER JOIN Plates ON Inventory.ItemType = 'Plates' AND Plates.Id = Inventory.ItemId LEFT OUTER JOIN Cups ON Inventory.ItemType = 'Cups' AND Cups.Id = Inventory.ItemId LEFT OUTER JOIN Flatware ON Inventory.ItemType = 'Flatware' AND Flatware.Id = Inventory.ItemId WHERE Inventory.id IN (2, 4, 5) In the query above, the joins on both the Plates and Flatware table are superfluous because those rows are never selected. I'm not sure I can get out of specifying each possible case in column list part of the query but it seems to me like it should be possible to only join those tables that are relevant based on the conditions set in the WHERE clause. Is something like this even possible? thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org