CODEWITHSUNDEEP
sqloracle-databaseoracle11ginner-join
Chris M
Chris M

Reputation: 143

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

Upvotes: 0

Views: 5118

Answers (2)

Chris M
Chris M

Reputation: 143

This query worked for me. I fixed my join like APC stated and I was able to get my desired results. Here is my final query:

SELECT DISTINCT
   FIRST_VALUE(dp.part_name)
      OVER(PARTITION BY dp.part_id) as Part_Name,
      FIRST_VALUE(dp.mfg_cage_code) OVER (PARTITION BY dp.part_id) as CAGE
  ,dp.part_id as Part_Number
FROM designed_parts dp
    inner join hardware_parts hp on dp.part_id = hp.part_id
        AND dp.mfg_cage_code = hp.mfg_cage_code
        AND dp.data_version_name = hp.data_version_name
    inner join part_instances pin on hp.part_id = pin.part_id 
        AND hp.mfg_cage_code = pin.mfg_cage_code
        AND hp.data_version_name = pin.data_version_name
    inner join part_instance_flight_phases pif on pin.part_instance_nmbr = pif.part_instance_nmbr
        AND pin.part_id = pif.part_id
        AND pin.mfg_cage_code = pif.mfg_cage_code
        AND pin.data_version_name = pif.data_version_name
    inner join flight_phases fp on pif.flight_nmbr = fp.flight_nmbr
        AND pif.flight_phase_name = fp.flight_phase_name
        AND pif.data_version_name = fp.data_version_name
    inner join flights f on fp.flight_nmbr = f.flight_nmbr 
        AND fp.data_version_name = f.data_version_name
WHERE dp.data_version_name = 'WORKING'
    AND ( 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)
ORDER BY dp.part_id ASC

Upvotes: 0

APC
APC

Reputation: 146309

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.

Upvotes: 2

Related Questions