Selecting same column in same table twice with different cri

Selecting same column in same table twice with different criteria in one query

I need to get part_id as first column next six columns based on process_id=21 and next six columns based on process_id=42 and so on..it retrieves duplicate records.Please help

     SELECT   DISTINCT A.PART_ID,
                  A.resources,
                  A.SCRAP_PCT,
                  A.FPLT,
                  A.ATP_FAMILY,
                  A.rfgo_family,
                  A.gfgo_family,
                  B.Sec_Resources,
                  B.SEC_SCRAP_PCT,
                  B.SEC_FPLT,
                  B.SEC_atp_family,
                  B.SEC_rfgo_family,
                  B.SEC_gfgo_family
FROM       (SELECT   DISTINCT a.PART_ID,
                                  f.name Resources,
                                  a.SCRAP_PCT,
                                  a.FPLT,
                                  e.NAME atp_family,
                                  c.NAME rfgo_family,
                                  d.NAME gfgo_family
                  FROM   s_ftl_line_mfg_process_map a,
                         s_ft_part_params b,
                         s_ft_rfgo_family c,
                         s_ft_gfgo_family d,
                         s_ft_atp_family e,
                         S_FTL_MACHINE_RESOURCE f
                 WHERE       f.name IS NOT NULL
                         AND a.part_id = b.part_id
                         AND b.rfgo_family_id = c.rfgo_family_id(+)
                         AND b.gfgo_family_id = d.gfgo_family_id(+)
                         AND b.atp_family_id = e.atp_family_id(+)
                        -- AND a.part_id = f.part_id(+)
                         AND A.PROCESS_ID = '21') A
            JOIN
               (SELECT   DISTINCT a.PART_ID,
                                  f.name Sec_Resources,
                                  a.SCRAP_PCT SEC_SCRAP_PCT,
                                  a.FPLT SEC_FPLT,
                                  e.NAME SEC_atp_family,
                                  c.NAME SEC_rfgo_family,
                                  d.NAME SEC_gfgo_family
                  FROM   s_ftl_line_mfg_process_map a,
                         s_ft_part_params b,
                         s_ft_rfgo_family c,
                         s_ft_gfgo_family d,
                         s_ft_atp_family e,
                         S_FTL_MACHINE_RESOURCE f
                 WHERE       f.name IS NOT NULL
                         AND a.part_id = b.part_id(+)
                         AND b.rfgo_family_id = c.rfgo_family_id(+)
                         AND b.gfgo_family_id = d.gfgo_family_id(+)
                         AND b.atp_family_id = e.atp_family_id(+)
                       -- AND a.part_id = f.part_id(+)
                         AND A.PROCESS_ID = '42') B
            ON A.part_id = B.part_id

Thanks,
Crystal

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi13y ago
hi,
I am not fully understand your question.
but in your query so many table are used and it might be possible some other table join resulting duplicate record.
and also it might possible your query resulting cross join between you two id data.