CODEWITHSUNDEEP
oracle-database
JavaSheriff
JavaSheriff

Reputation: 7665

Oracle Inner join - Select One row from right table

I have the following table layout

ACCIDENTS contains the accident ID
ACCIDENT_TO_CAR contains relations between accidents and car plates
REPORT contains the car plates and driver names

Using the join below:

SELECT T_ACCIDENTS.ACCIDENT_ID,DRIVER_NAME
from T_ACCIDENTS
          inner join T_ACCIDENT_TO_CAR on T_ACCIDENT_TO_CAR.ACCIDENT_ID = T_ACCIDENTS.ACCIDENT_ID 
          inner join T_REPORT  on T_ACCIDENT_TO_CAR.PLATE = T_REPORT.PLATE 
and DRIVER_NAME in ('Foo','Moo')

Currently Driver 'Foo' appears twice in the report because two of his cars had an accident.

How can limit the result of the join the driver by getting a single record ACCIDENT_TO_CAR so in other words distinct on the report based on the driver

Test data:

CREATE TABLE "T_ACCIDENTS" ("ACCIDENT_ID" VARCHAR2(20))

Insert into T_ACCIDENTS (ACCIDENT_ID) values ('1');
Insert into T_ACCIDENTS (ACCIDENT_ID) values ('2');
Insert into T_ACCIDENTS (ACCIDENT_ID) values ('3');



 CREATE TABLE "T_ACCIDENT_TO_CAR" 
   (    
   "PLATE" VARCHAR2(20 BYTE), 
    "ACCIDENT_ID" VARCHAR2(20 BYTE)
   ) 

Insert into T_ACCIDENT_TO_CAR (PLATE,ACCIDENT_ID) values ('1','1');
Insert into T_ACCIDENT_TO_CAR (PLATE,ACCIDENT_ID) values ('2','1');
Insert into T_ACCIDENT_TO_CAR (PLATE,ACCIDENT_ID) values ('4','2');
Insert into T_ACCIDENT_TO_CAR (PLATE,ACCIDENT_ID) values ('3','1');
Insert into T_ACCIDENT_TO_CAR (PLATE,ACCIDENT_ID) values ('2','2');



CREATE TABLE "T_REPORT" 
   (    
    "PLATE" VARCHAR2(20 BYTE), 
    "DRIVER_NAME" VARCHAR2(20 BYTE)
   )

Insert into T_REPORT (PLATE,DRIVER_NAME) values ('1','Foo');
Insert into T_REPORT (PLATE,DRIVER_NAME) values ('2','Foo');
Insert into T_REPORT (PLATE,DRIVER_NAME) values ('3','Moo');
Insert into T_REPORT (PLATE,DRIVER_NAME) values ('4','Bar');

UPDATE I am sorry,the Data set and query was not correct, I fixed!

Upvotes: 0

Views: 266

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

Use the DISTINCT keyword to tell Oracle to only include unique records.

SELECT DISTINCT T_ACCIDENTS.ACCIDENT_ID,T_REPORT.DRIVER_NAME
from T_ACCIDENTS
          inner join T_ACCIDENT_TO_CAR on T_ACCIDENT_TO_CAR.ACCIDENT_ID = T_ACCIDENTS.ACCIDENT_ID 
          inner join T_REPORT  on T_ACCIDENT_TO_CAR.PLATE = T_REPORT.PLATE;

UPDATE BASED ON CLARIFIED REQUIREMENTS

GROUP BY ACCIDENT_ID and select the driver with the first name alphabetically.

SELECT T_ACCIDENTS.ACCIDENT_ID, MIN(DRIVER_NAME) DRIVER_NAME
from T_ACCIDENTS
          inner join T_ACCIDENT_TO_CAR on T_ACCIDENT_TO_CAR.ACCIDENT_ID = T_ACCIDENTS.ACCIDENT_ID 
          inner join T_REPORT  on T_ACCIDENT_TO_CAR.PLATE = T_REPORT.PLATE 
and DRIVER_NAME in ('Foo','Moo')
GROUP BY T_ACCIDENTS.ACCIDENT_ID;

Upvotes: 2

Related Questions