SQL: Join on columns with OR

I have two types of items, let's say they're Cars and Passengers. I have a table for cars, and a table for passengers.

I want to be able to store associations of these items, and I'm using another table for this, called "Pairings". The schema looks like this:

Item1ID INT,
Item1Type INT,
Item2ID TEXT,
Item2Type INT,
PRIMARY KEY(Item1ID, Item2ID)

The Item1Type and Item2Type columns hold an enum value used to determine which type an item is when looking it up. Eg: If I want to associate Car1 with Passenger A and Passenger B, the table could have two entries:

Item1ID,       Item1Type,     Item2ID,       Item2Type 
Car1           ItemTypes.Car  PassengerA     ItemTypes.Passenger
Car1           ItemTypes.Car  PassengerB     ItemTypes.Passenger

The problem I have is that I also want to associate cars with passengers, so PassengerB could be associated with car C and car D, but also with car A. I already have an entry for Car A and these two extra associations to map PassengerB to CarC and CarD:

Item1ID,     Item1Type,           Item2ID,   Item2Type 
PassengerB   ItemTypes.Passeng开发者_开发技巧er  CarC       ItemTypes.Car
PassengerB   ItemTypes.Passenger  CarD       ItemTypes.Car

What I need to do is find all of the associated entries for Passenger B, so in sort of Pseudo SQL (using SQlite), this would be:

SELECT * FROM Cars c 
          LEFT JOIN Pairings p ON 
          (c.CarID = p.Item1ID AND p.Item1Type = @CarPairingType) 
       OR (c.CarID = p.Item2ID AND p.Item2Type = @CarPairingType)

So I need to do an inner join on either field, sort of like a switch, depending on whether the type is 0 or 1. Is this possible, or is there a better way to approach this?

Why don't you just change the Item1ID and Item2ID to carID and passengerID ?

So you will need only 2 columns in the table and add the two rows like this?

carID,       passengerID,
Car1           PassengerA
Car1           PassengerB

(mapping PassengerB to CarC and CarD):

CarC       PassengerB
CarD       PassengerB

Maybe a UNION would be "future proof"?

SELECT * FROM Cars c 
INNER JOIN Pairings p ON c.CarID = p.Item1ID AND p.Item1Type = @CarPairingType
UNION
SELECT * FROM Cars c 
INNER JOIN Pairings p ON c.CarID = p.Item2ID AND p.Item2Type = @CarPairingType

Taking your design at face value, the correct way of spelling OR in this context is UNION:

SELECT *
  FROM Cars c 
  LEFT JOIN (SELECT p1.Item1ID AS CarID
               FROM Pairings AS p1
              WHERE p1.Item1Type = @CarPairingType
              UNION
             SELECT p2.Item2ID AS CarID
               FROM Pairings AS p2
              WHERE p2.Item2Type = @CarPairingType
            ) AS p ON c.CarID = p.CarID

It is not clear that you actually want a LEFT JOIN; I would expect to just use a plain (INNER) JOIN.

I'm not convinced the design is good; drop the item types as suggested by ypercube. Agile programming has a number of motto phrases, one of which is YAGNI - You Ain't Gonna Need It. While it is good to keep an eye on the future, I'm not sure that you would benefit from the extending the current design as hinted in your comment.