Home » SQL & PL/SQL » SQL & PL/SQL » Avoid Duplicate
Avoid Duplicate [message #385053] Fri, 06 February 2009 05:27 Go to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi

I have 3 tables , and iam selecting the date from 3 table,but in the output iam getting duplicate rows.

so how i can avoid the duplicate rows in select statement

Re: Avoid Duplicate [message #385055 is a reply to message #385053] Fri, 06 February 2009 05:34 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Join the tables properly.

If you want a better answer than that you'll need to post the table definitions and your current query.
Re: Avoid Duplicate [message #385058 is a reply to message #385053] Fri, 06 February 2009 05:38 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
any other solution?
Re: Avoid Duplicate [message #385060 is a reply to message #385053] Fri, 06 February 2009 05:39 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
add the DISTINCT keyword?
Re: Avoid Duplicate [message #385061 is a reply to message #385053] Fri, 06 February 2009 05:40 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
yes i have done this, still its showing duplicate record
Re: Avoid Duplicate [message #385067 is a reply to message #385061] Fri, 06 February 2009 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
saic wrote on Fri, 06 February 2009 12:40
yes i have done this, still its showing duplicate record

It can't be or you have another definition of duplicates than standard one but as you are reluctant to post useful information to help you we can't help you.
The only thing we can say is: fix your query.

Regards
Michel

Re: Avoid Duplicate [message #385068 is a reply to message #385053] Fri, 06 February 2009 05:55 Go to previous messageGo to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
check your join condition properly
that might be cartesion product
Re: Avoid Duplicate [message #385372 is a reply to message #385053] Mon, 09 February 2009 05:43 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
Hi,

still iam faceing the prob,

iam not able to give the tables and scripts,because i have access only production DB, in that we can't create table and any other stuff.

so iam explaining my prob, please understand this

i have 4 table(a,b,c,d),in that iam writing one select statement

A table B table C table D table
id name loc date
name loc name sal
date


select a.id,b,name,c.loc,d.date from a,b,c,d
where
a.id=12 and here its giving 1row
a.id=b.id and same 1 row
b.loc=c.loc and but here giving 200row but i need onyl one row
a.date=d.date

could You pleae give any suggestions.
Re: Avoid Duplicate [message #385376 is a reply to message #385372] Mon, 09 February 2009 05:52 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

select a.id,b,name,c.loc,d.date from a,b,c,d
where
a.id=12 and here its giving 1row
a.id=b.id and same 1 row
b.loc=c.loc and but here giving 200row but i need onyl one row
a.date=d.date


Then do it. It's you who know the data model and it's you who know how the data is. We don't have any clue apart from the fact you claim you are getting "duplicates" and you want only one row. You should analyze the data and try to find out which row you are interested and filter it accordingly.

Since you are reluctant to give us any information about the datamodel and underlying test data, I am sorry to say you are on your own.

Regards

Raj
Re: Avoid Duplicate [message #385377 is a reply to message #385053] Mon, 09 February 2009 05:57 Go to previous messageGo to next message
saic
Messages: 27
Registered: December 2008
Junior Member
yes iam agree with Raj,

but i dont know how to analyze the data and table structre

could you please give some ideas on that.
Re: Avoid Duplicate [message #385386 is a reply to message #385053] Mon, 09 February 2009 06:14 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
What's your definition of duplicates? Are you only selecting the dates? Note that the dates are not duplicates if you for instance have one date as 15-nov-08 11:12 and another as 15-nov-08 11:13. They are different, and the distinct will give two rows. Use trunc if you only want to see the year-month-day part of the date column.
select distinct date_col
from ((select to_date('15-nov-08 11:12', 'DD-MON-YY HH24:MI') date_col from dual)union all
      (select to_date('15-nov-08 11:13', 'DD-MON-YY HH24:MI') date_col from dual));

Result (Note that it might look like a duplicate when not showing the time, even though it's not):
15-NOV-08
15-NOV-08
Previous Topic: Need a simple(?) logic
Next Topic: Getting Data From the web without PL/SQL
Goto Forum:
  


Current Time: Mon Dec 05 23:56:44 CST 2016

Total time taken to generate the page: 0.14555 seconds