How to relate a double indexed table?

izz · August 18th, 2003, 05:54 AM

I have three tables:
Favorates, table1, table2

Favorates contain (UserName, ItemID, TypeID)
Table1 and table1 contain the detailed information about (ItemID) in favorates depending on it's type. If ItemId type is 1 then details are in table1, if ItemID type is 2 Then details are in table2.

Favoraes table key is (UserName+ItemID), i.e, one uername cannot have the same item more than once.

How can I create the relation between favorates table and the other two? ItemID value is always either a key of table one or table 2??

I would be thankfull to whoever help,,,

If you don't have the one you love, love the one you have

Jeff Mason · August 18th, 2003, 06:21 AM

Use an OUTER JOIN to combine both tables with the Favorites table:

Code:

SELECT UserName, ItemID, CASE 
            WHEN TypeID=1 THEN Table1.Details
            WHEN TypeID=2 THEN Table2.Details
            END as TheDetails
    FROM Favorites
        LEFT JOIN Table1 ON Favorites.ItemID=Table1.ItemID
        LEFT JOIN Table2 ON Favorites.ItemID=Table2.ItemID;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com