Kaspar Kaspar - 12 days ago 6
SQL Question

SQL query over two tables

I am having problems with queries:

My tables are following:

Drivers

|DRIVER_ID|FIRST_NAME|LAST_NAME|AGE|
| 1|John |Smith |19 |
| 2|Steve |Oak |33 |
| 3|Mary |Sanchez |22 |


Drivers_in_Teams

|DRIVERS_IN_TEAMS_ID|DRIVER_ID|TEAM_ID|BEG_DATE |END_DATE |
| 1| 1| 1|18-NOV-05| - |
| 2| 3| 2|10-APR-12| - |
| 3| 2| 3|19-JUL-01|02-AUG-04|


BEG_DATEs are done with "sysdate-number"

I would like to make one query, where it displays the oldest driver with first and last name, who is still in a team.

I tried some examples which I have found from google, but I can't get them to work. I'm beginner in SQL and I have no idea how to do this query.

an example I found

SELECT FIRST_NAME, LAST_NAME FROM DRIVER, DRIVERS_IN_TEAMS WHERE DRIVER.DRIVER_ID = DRIVERS_IN_TEAMS.DRIVER_ID AND DRIVERS_IN_TEAMS.BEG_DATE = SELECT (SELECT MIN(BEG_DATE) FROM DRIVERS_IN_TEAMS) (SELECT MAX(AGE) FROM DRIVERS);

Answer

I think it should work. No resources to test and debug. Let me know if there are any problems.

SELECT first_name FROM driver WHERE
driver_id in (select unique(driver_id) from driver_in_teams where end_date is NULL) 
and age =(select MAX(age) from drivers)

Alternative:

SELECT first_name FROM driver d
INNER JOIN
driver_in_teams dt ON (d.driver_id=dt.driver_id)
WHERE dt.END_DATE IS NULL
AND d.age=(select MAX(age) from drivers)
Comments