Search code examples
sqloracle-databaseoracle11ginner-join

ORACLE SQL query returning duplicate data with inner join across 6 tables

I am using ORACLE 11G. Also using Oracle application express 4.2. I have a SQL query that is returning duplicates. I am using inner join to join mulitple tables. I am joining 6 tables total. I provided a screenshot of my table schema below. I have sample duplicate data below as well. What might be the reason that is causing me to get hundreds of rows of duplicate data? Any help is appeciated.

Here is my query below:

SELECT f.flight_nmbr, dp.part_id, dp.mfg_cage_code, pif.flight_phase_name, dp.part_name
   FROM flights f
       inner join flight_phases fp on f.flight_nmbr = fp.flight_nmbr
       inner join part_instance_flight_phases pif on fp.flight_nmbr = pif.flight_nmbr
       inner join part_instances pin on pif.part_instance_nmbr = pin.part_instance_nmbr
       inner join hardware_parts hp on pin.part_id = hp.part_id
       inner join designed_parts dp on hp.part_id = dp.part_id
       order by dp.part_id asc;

Here is the same query just with ORACLE APEX stuff added:

SELECT f.flight_nmbr, dp.part_id, dp.mfg_cage_code, pif.flight_phase_name, dp.part_name
   FROM flights f
     inner join flight_phases fp on f.flight_nmbr = fp.flight_nmbr
     inner join part_instance_flight_phases pif on fp.flight_nmbr = pif.flight_nmbr
     inner join part_instances pin on pif.part_instance_nmbr = pin.part_instance_nmbr
     inner join hardware_parts hp on pin.part_id = hp.part_id
     inner join designed_parts dp on hp.part_id = dp.part_id
   WHERE ( f.flight_nmbr = :P100_FLIGHT_LOV1) 
     OR ( f.flight_nmbr = :P100_FLIGHT_LOV2) 
     OR ( f.flight_nmbr = :P100_X) 
     OR ( f.flight_nmbr = :P100_X2)

Here is the duplicate data below:

Part_ID          Cage   Part_name   Flight_phase
-------------- ------   ---------   ------------
52S SE592F-52S   21356  SOYUZ MS    LAUNCH 
52S SE592F-52S   21356  SOYUZ MS    RETURN 
52S SE592F-52S   21356  SOYUZ MS    LAUNCH 
52S SE592F-52S   NAMWG  SOYUZ       LAUNCH 
52S SE592F-52S   NAMWG  SOYUZ       RETURN 
52S SE592F-52S   NAMWG  SOYUZ       LAUNCH 
52S SE592F-52S   21356  SOYUZ MS    LAUNCH 
52S SE592F-52S   21356  SOYUZ MS    RETURN 
52S SE592F-52S   21356  SOYUZ MS    LAUNCH

This is the correct output that my data should display:

Part_ID             Cage    Part_name           Flight_phase
--------------      ------ ---------            ------------
СПМ-9061-60         RSA00  SLEEPING BAG         LAUNCH 
SE592F-52S          21356  SOYUZ MS             LAUNCH
ATHSOYUZTMA         NAMWG  SOYUZ TMA VEHICLE    ORBIT
SE592F              NAMWG  SOYUZ VEHICLE        ORBIT

Here is a screenshot of schema below:

enter image description here

Solution

  • All of your primary keys are compound keys, comprising more than one column. But all of your joins are on a single column. This means your result set is a product, with one row for every combination of column instances.

    For instance the join between flights and flight_phases should join on flight_nmbr, data_version_nmbr. So because you only join on flight_nmbr you will get "duplicate" records for each combination of data_version_nmbr per flight_nmbr in each table.

    The solution is obvious: re-edited the joins to include all the columns in the foreign key references.