Home » Other » Training & Certification » getting duplicates from query (9.0.4.0)
getting duplicates from query [message #310357] Tue, 01 April 2008 00:47 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I wrote one query.But I am getting duplicates.But I don't want those duplicate records. I need exact record.can you please look into the query and give me the suggestion to make changes..
select b.buyer,b.buyer_name,m.item,m.item_desc,m.status,       m.class from Buyer b,dept d,master m,
       subclass sc
where  d.buyer = b.BUYER
and    m.class =  sc.class

O/P
BUYER	BUYER_NAME ITEM	   ITEM_DESC	STATUS	CLASS

320	DAVID      1900     SONY_TV     A	29
320	DAVID      1900     SONY_TV     A	29
320	DAVID      1900     SONY_TV     A	29
320	DAVID      1900     SONY_TV     A	29
320	DAVID      1900     SONY_TV     A	29
128     Bernards   3200     LG_System   A       18
128     Bernards   3200     LG_System   A       18
128     Bernards   3200     LG_System   A       18
212     Kris       3299     WMachine    A       21
432     Fran       9087     Rapis       A       32
432     Fran       9087     Rapis       A       32
432     Fran       9087     Rapis       A       32

So here I don't want to display these many duplicates. I want to display the distinct values.Please guide me..

Thank you

[Updated on: Tue, 01 April 2008 00:47]

Report message to a moderator

Re: getting duplicates from query [message #310358 is a reply to message #310357] Tue, 01 April 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DISTINCT.

Regards
Michel
Re: getting duplicates from query [message #310362 is a reply to message #310358] Tue, 01 April 2008 00:53 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel I have tried that one also. But for executing it's taking more time.Is there any other way to write this query.
before using "DISTINCT" it's taking 7 secs.But using "DISTINCT" it's taking 6.5 mins.please look into this..

Regards.
Re: getting duplicates from query [message #310374 is a reply to message #310357] Tue, 01 April 2008 01:10 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You have 4 tables in the FROM clause.
You use columns from 2 tables in the SELECT clause.
You have only 2 join condition in the WHERE clause.

Add JOIN condition for the other tables (at least one more). You may use ANSI JOIN syntax to prevent missing any of them.
Alternatively remove from the FROM clause the tables which are not used in the query at all.

Only you can figure out the missing condition(s), as only you know the table relationship (columns and primary/foreign keys).
Re: getting duplicates from query [message #310375 is a reply to message #310362] Tue, 01 April 2008 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course it takes more time as you ask Oracle to do more work.
It has to search for and remove all duplicates.
Check if sorts are made in memory otherwise increase space for them then you will have better performances.

Regards
Michel
Re: getting duplicates from query [message #310382 is a reply to message #310374] Tue, 01 April 2008 01:18 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I have added one more condition
select b.buyer,b.buyer_name,m.item,m.item_desc,m.status,       m.class from Buyer b,dept d,master m,
       subclass sc
where  d.buyer    = b.BUYER
and    m.class    =  sc.class
and    m.subclass = sc.subclass

o/p
BUYER	BUYER_NAME	ITEM	ITEM_DESC	STATUS	CLASS

36	CLAIRE  	24293	 NECKLACE	A	4903
36	CLAIRE  	24293	 NECKLACE	A	4903
36	CLAIRE  	24293	 NECKLACE	A	4903
36	CLAIRE  	24293	 NECKLACE	A	4903
18      ROCK            32421    BANGL          A       8907
21      CANE            89763    GYMITEMS       A       9879
21      CANE            89763    GYMITEMS       A       9879
21      CANE            89763    GYMITEMS       A       9879


still am getting duplicates.
pls look into this/.
Re: getting duplicates from query [message #310383 is a reply to message #310382] Tue, 01 April 2008 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No change for me.

Regards
Michel
Re: getting duplicates from query [message #310386 is a reply to message #310383] Tue, 01 April 2008 01:25 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,
here i have added one more join conditon .
i.e
where....
m.subclass=sc.subclass

so now we have 4 tables with three join conditons.am i right?
still am facing the problem.please look into this.
Re: getting duplicates from query [message #310392 is a reply to message #310386] Tue, 01 April 2008 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check if you have other missing conditions to uniquely identify your rows.
If there is none, you have to use DISTINCT.

Regards
Michel

Re: getting duplicates from query [message #310396 is a reply to message #310392] Tue, 01 April 2008 01:48 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

Using distinct it's taking more time for execution..Join coonditions are ok.
Re: getting duplicates from query [message #310398 is a reply to message #310392] Tue, 01 April 2008 01:50 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

Using distinct it's taking more time for execution..Join coonditions are ok.
Michel these are the structures of the tables
Master : Class number(20) [Master and Subclass tables have "class" and "Subclass " columns are common] Subclass number(20) item number(20) item_desc varchar2(30) status varchar2(5) level varchar2(20)subclass : Class number(20) Subclass number(20)Dept : buyer number(20) deptno number(10) dname varchaR2(20) [in Buyer and Dept " Buyer" column is common]Buyer : buyer number(20) buyer_name varchar2(20)


Thank you.

[Updated on: Tue, 01 April 2008 02:47] by Moderator

Report message to a moderator

Re: getting duplicates from query [message #310401 is a reply to message #310398] Tue, 01 April 2008 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't read lines that are thousand characters wide. Modify your previous post.

This is what I said: use [join] conditions to restrict the rows then when you have used all the conditions you can, if there are still duplicates you have to use DISTINCT.

You know your model, you know your data, you know what you have to do.

Regards
Michel


Re: getting duplicates from query [message #310402 is a reply to message #310401] Tue, 01 April 2008 02:02 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

am sorry Michel by mistake it was happened.
here again i post the structure of the tables.
[pre]
Michel,

Using distinct it's taking more time for execution..Join coonditions are ok.
Michel these are the structures of the tables
Master  :   Class  number(20)   [Master and Subclass tables   
                                have "class" and "Subclass " 
                                 common columns]
             Subclass number(20)
             item number(20)
             item_desc   varchar2(30)                      
             status  varchar2(5)
             level    varchar2(20)
subclass :   Class  number(20)
             Subclass number(20)
Dept     :   buyer number(20)
             deptno  number(10)
             dname  varchaR2(20)     [in Buyer and Dept "Buyer" 
                                      "buyer" is common column]

Buyer :      buyer number(20)                        
             buyer_name varchar2(20)


Thank you. 

[Updated on: Tue, 01 April 2008 02:03]

Report message to a moderator

Re: getting duplicates from query [message #310404 is a reply to message #310357] Tue, 01 April 2008 02:11 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Add JOIN condition for the other tables (at least one more).

Unfortunately you added JOIN condition on tables which are already joined. It is good, however you still have two pairs of tables (D+B and M+SC) not joined. You still need join between those two pairs.
Otherwise you will have all M+SC combinations for every D+B combination. Also I do not see any reason why you introduced DEPT and SUBCLASS tables as you do not SELECT from them and they do not add anything new. However it will not reduce the number of rows much, you still have to state (at least in words), on which condition MASTER and BUYER shall match. I do not see any.
Re: getting duplicates from query [message #310418 is a reply to message #310404] Tue, 01 April 2008 03:38 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,Now I wrote the query as follows
SELECT b.buyer,b.buyer_name,m.item,m.item_desc,m.status,
       m.class,m.subclass
FROM   buyer       b ,
       master      m ,
       dept        d
WHERE  d.buyer   = b.buyer
AND    d.dept    = m.dept;


Still am getting duplicates....Please look into this.


[Mod-edit: Please use [code] instead of [pre] tags]

[Updated on: Tue, 01 April 2008 04:46] by Moderator

Report message to a moderator

Re: getting duplicates from query [message #310420 is a reply to message #310418] Tue, 01 April 2008 03:43 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Get a piece of paper, draw squares representiong your tables, and inside those squares, list the columns. Now draw the lines in that your joins (in your sql code above) represent i.e. when you see
d.buyer = b.buyer in your code, draw a line from table d column buyer to table d column buyer. Notice that there are tables that do not have lines drawn between them. This means that they are nopt joined. To avoid the cartesiian product that is generating many rows, you need to make sure that all of the tables are joined in some way.
Re: getting duplicates from query [message #310421 is a reply to message #310420] Tue, 01 April 2008 03:47 Go to previous message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Thanks to all giving these many ideas...Thank you very much.
Previous Topic: Which of the following is not considered part of an Oracle database?
Next Topic: TRIGGER FOR UPDATING ROW IF EXISTS AND INSERT NEW ROW IF ALREADY EXIST
Goto Forum:
  


Current Time: Sat Dec 10 20:17:16 CST 2016

Total time taken to generate the page: 0.08748 seconds