| getting duplicates from query [message #310357] |
Tue, 01 April 2008 00:47  |
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 #310362 is a reply to message #310358] |
Tue, 01 April 2008 00:53  |
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 |
flyboy
Messages: 1903
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 #310382 is a reply to message #310374] |
Tue, 01 April 2008 01:18 |
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 #310386 is a reply to message #310383] |
Tue, 01 April 2008 01:25 |
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 #310398 is a reply to message #310392] |
Tue, 01 April 2008 01:50 |
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 #310402 is a reply to message #310401] |
Tue, 01 April 2008 02:02 |
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 |
flyboy
Messages: 1903
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 |
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 |
pablolee
Messages: 2882
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
