ORACLE SQL Error - Missing Right Parenthesis - Query

Question

I tried to search for answers online and around here unfortunately it can't solve the problem.

Here is my code:

SELECT d.driverID, 
       e.firstname, 
       e.lastname, 
       t.testid, 
       t.testType
 FROM driver d
 LEFT JOIN employee e 
   ON (e.employeeID = d.employeeID)
INNER JOIN driver_test dt 
   ON (d.driverID = dt.driverID  WHERE dt.testDate(BETWEEN TO_DATE('01-JAN-2012', 'dd-mm-yyyy') AND TO_DATE('31-JAN-2012', 'dd-mm-yyyy')))
RIGHT JOIN test t ON dt.testID = t.testID WHERE (t.testType='Alcohol');

The problem lies on Line 4. It says missing right parenthesis. I tried adding additional ones but the problem still persists.

P.S

I'm a student currently studying Oracle SQL so my knowledge is still lacking.

Answer 1

There are a couple of errors in your statement

• to join on more than one condition, you have to use AND, not WHERE
• get rid of the ( between the column name and BETWEEN

Fixed query:

SELECT d.driverID,
   e.firstname,
   e.lastname,
   t.testid,
   t.testType
FROM driver d
  LEFT JOIN employee e
    ON (e.employeeID = d.employeeID)
 INNER JOIN driver_test dt
    ON (d.driverID = dt.driverID AND
       dt.testDate BETWEEN TO_DATE('01-JAN-2012',
                                    'dd-mm-yyyy') AND
       TO_DATE('31-JAN-2012',
                'dd-mm-yyyy'))
 RIGHT JOIN test t
    ON dt.testID = t.testID
 WHERE (t.testType = 'Alcohol');