Home » SQL & PL/SQL » SQL & PL/SQL » Using JOIN to prevent duplicate??
icon6.gif  Using JOIN to prevent duplicate?? [message #305759] Tue, 11 March 2008 22:24 Go to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Here is my script...

SELECT distinct
FRAME_UNITS.FRAU_POSITION ADSL_BAR_IDENT_CODE,
dwhdv.NIS_ADSLBAR.TOT_BAR_SPARE_CTR(frau_id)TOT_BAR_SPARE_CTR,
dwhdv.NIS_ADSLBAR.TOT_BAR_WORK_CTR (frau_id) TOT_BAR_WORK_CTR,
dwhdv.NIS_ADSLBAR.TOT_BAR_ASSIGN_CTR (frau_id) TOT_BAR_ASSIGN_CTR,
dwhdv.NIS_ADSLBAR.AUTHORIZATION_CODE(equp_locn_ttname) AUTHORIZATION_CODE,
dwhdv.NIS_ADSLBAR.TOT_BAR_DEFECT_CTR (Frau_id) TOT_BAR_DEFECT_CTR,
dwhdv.NIS_ADSLBAR.TOT_BAR_RESERV_CTR (Frau_id) TOT_BAR_RESERV_CTR,
dwhdv.NIS_ADSLBAR.TOT_BAR_TERM_CTR (Frau_id) TOT_BAR_TERM_CTR,
dwhdv.NIS_ADSLBAR.COMMISION_DATE (equp_locn_ttname) COMMISION_DATE,
FROM  FRAME_UNITS,
FRAME_APPEARANCES,
FRAME_CONTAINERS,
EQUIPMENT,
LOCATIONS
WHERE FRAME_APPEARANCES.FRAA_FRAU_ID = FRAME_UNITS.FRAU_ID
AND FRAME_UNITS.FRAU_FRAC_ID = FRAME_CONTAINERS.FRAC_ID
AND FRAME_CONTAINERS.FRAC_LOCN_TTNAME = LOCATIONS.LOCN_TTNAME
AND LOCATIONS.LOCN_TTNAME = EQUIPMENT.EQUP_LOCN_TTNAME
AND FRAME_UNITS.FRAU_NAME like '%DSL%'
AND EQUIPMENT.EQUP_EQUT_ABBREVIATION LIKE '%DSLAM%';

My problem is that there are more than one FRAME_UNITS.FRAU_POSITION in the result and some of them are being doubled i.e duplicate row entry.

So, my question is.. Is there a way of JOIN that could prevent the duplicate result of the query?

I don't want SELECT DISTINCT coz there are more than 10 000 rows returned by the query and yet only a few hundred rows is unique.

Thank you.
Re: Using JOIN to prevent duplicate?? [message #305760 is a reply to message #305759] Tue, 11 March 2008 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>My problem is that there are more than one FRAME_UNITS.FRAU_POSITION in the result
Place a UNIQUE index on the FRAME_UNITS.FRAU_POSITION to prevent the duplicates getting into the table.
Re: Using JOIN to prevent duplicate?? [message #305764 is a reply to message #305760] Tue, 11 March 2008 22:45 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
anacedent wrote on Wed, 12 March 2008 11:32
>My problem is that there are more than one FRAME_UNITS.FRAU_POSITION in the result
Place a UNIQUE index on the FRAME_UNITS.FRAU_POSITION to prevent the duplicates getting into the table.

Thanks ana.

But actually that is not the case.

This is due to the fact that all other columns in the FRAME_UNITS table are different. Thus, they are no duplicate rows inside that table. In fact, it has another column as a primary key for that table - FRAU_ID.

But this happen coz we only take the FRAU_POSITION column from FRAME_UNITS where it appears more than once for each FRAU_POSITION as it has different FRAU_ID (primary key). Whereas the combination of column from the query above would return some duplicate rows.

So, how is that? Sad

[Updated on: Tue, 11 March 2008 22:47]

Report message to a moderator

Re: Using JOIN to prevent duplicate?? [message #305770 is a reply to message #305764] Tue, 11 March 2008 23:32 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
You are writing a query that returns more the 10K rows and makes 8 function calls per row, thus making over 80K function calls, and you somehow think that a DISTINCT is going to be a performance issue?
Re: Using JOIN to prevent duplicate?? [message #305775 is a reply to message #305770] Wed, 12 March 2008 00:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
FROM  (
    SELECT DISTINCT col1, col2, ...
    FROM   FRAME_UNITS
) FRAME_UNITS,
FRAME_APPEARANCES,
FRAME_CONTAINERS,
EQUIPMENT,


Ross Leishman
Re: Using JOIN to prevent duplicate?? [message #305794 is a reply to message #305775] Wed, 12 March 2008 01:12 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
or remove FRAME_APPEARANCES from your table-list and add
where exists (select 1 
              from   FRAME_APPEARANCES fap
              where  fap.FRAA_FRAU_ID = FRAME_UNITS.FRAU_ID
             )
Previous Topic: 32 bit oracle client with 64 bit oracle server
Next Topic: Not able to drop a table
Goto Forum:
  


Current Time: Wed Dec 07 06:37:51 CST 2016

Total time taken to generate the page: 0.13521 seconds