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/[email protected]
