MYSQL select statement conditions

Question

query:

SELECT u.deviceID, u.userName, u.contactNo, u.rating
  FROM User u
 INNER JOIN TaxiQuery t ON u.deviceID = t.seat1
                        OR u.deviceID = t.seat2
                        OR u.deviceID = t.seat3
                        OR u.deviceID = t.seat4
 WHERE t.queryID = 3;

+--------------------------------------+----------+-----------+--------+
| deviceID                             | userName | contactNo | rating |
+--------------------------------------+----------+-----------+--------+
| 00000000-0000-1000-8000-0016CB8B3C8E | uuuuuu   |     55555 |      5 |
+--------------------------------------+----------+-----------+--------+

describe user;

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| deviceID  | varchar(100) | NO   | PRI | NULL    |       |
| userName  | varchar(100) | YES  |     | NULL    |       |
| contactNo | int(11)      | YES  |     | NULL    |       |
| emailAddr | varchar(100) | YES  |     | NULL    |       |
| rating    | int(11)      | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

mysql> describe taxiQuery;

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| queryID      | int(11)      | NO   | PRI | NULL    | auto_increment |
| destination  | varchar(100) | YES  |     | NULL    |                |
| deptTime     | varchar(100) | YES  |     | NULL    |                |
| startingPt   | varchar(100) | YES  |     | NULL    |                |
| boardingPass | varchar(100) | YES  |     | NULL    |                |
| miscInfo     | varchar(100) | YES  |     | NULL    |                |
| seat1        | varchar(100) | YES  |     | NULL    |                |
| seat2        | varchar(100) | YES  |     | NULL    |                |
| seat3        | varchar(100) | YES  |     | NULL    |                |
| seat4        | varchar(100) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

What i want is to display the user's information if they exist in (seat1/seat2/seat3/seat4) in TaxiQuery. But i am only able to output one result when they are suppose to be three.

May i know how do i modify mysql statement to display the user's information when (seat1-4 is the foreign key to the deviceID of User's table) when seat1, seat2, seat3, seat4 contains the deviceID of the users?

Answer 1

As far as I can tell, it should work if you don't do an INNER join. I think the INNER keyword is telling mySQL to only include each source a maximum of once, so it will only use one copy of the TaxiQuery, when you actually need up to four (one per seat).