wood burning stoves 2.0*
The moose likes JDBC and the fly likes sql query returning duplicates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql query returning duplicates" Watch "sql query returning duplicates" New topic
Author

sql query returning duplicates

vamshi g
Greenhorn

Joined: Sep 12, 2008
Posts: 26
hi all i am retrieving data from three tables, i am getting duplicate values how to avoid these duplicates my query is

select url,title, b.ticket_name, b.networkcommission, c.commision from group_partner a,price b,partner_ticket_commision c where a.partnerid=3809 and a.partnerid=c.partnerid and evt_id=13840;

thank you.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

you aren't joining table b in your where clause, so you're getting row of a-c for every row of 'b'
vamshi g
Greenhorn

Joined: Sep 12, 2008
Posts: 26
how to change it,can you explain please.
than you.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by vamshi vamshi:
how to change it,can you explain please.
than you.


First you need to tell us how table b (price) is related with a (group_partner) and c (partner_ticket_commision)

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
vamshi g
Greenhorn

Joined: Sep 12, 2008
Posts: 26
there is no relation price and group_partner tables am just retrieving data using partnerid and eventid
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

"vamshi vamshi",
Please check your private messages.
-DOM
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by vamshi vamshi:
there is no relation price and group_partner tables am just retrieving data using partnerid and eventid


If there is no relation then I would suggest make a seperate call for price table.

Shailesh
vamshi g
Greenhorn

Joined: Sep 12, 2008
Posts: 26
ok lets take another query,

select a.url,a.title, b.ticket_name, c.commision , c.price_id, c.eventid from group_partner a,price b,partner_ticket_commision c where a.partnerid=3809 and c.partnerid=a.partnerid and c.price_id=b.price_id and b.evt_id=c.eventid and b.evt_id=43176.


in this i am getting 2 records,but url and title is repeates in 2 rows,
any solution to avoid duplicates.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Same problem, there is no join condtion between A & B so its giving duplicate records.

Shailesh
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

try this query ..

I have not tested it, so I can not promise it will work


SELECT
A.URL,
A.TITLE,
D.PARTNERID,
D.TICKET_NAME,
D.COMMISION ,
D.PRICE_ID,
D.EVENTID
FROM
GROUP_PARTNER A,
(SELECT
C.PARTNERID PARTNERID,
B.TICKET_NAME TICKET_NAME,
C.COMMISION COMMISION,
C.PRICE_ID PRICE_ID,
C.EVENTID EVENTID
FROM
PRICE B,
PARTNER_TICKET_COMMISION C
WHERE
C.PRICE_ID=B.PRICE_ID
AND
B.EVT_ID=C.EVENTID
AND
B.EVT_ID=43176.
) D
WHERE
A.PARTNERID = D.PARTNERID
AND
A.PARTNERID=3809


Shailesh
vamshi g
Greenhorn

Joined: Sep 12, 2008
Posts: 26
with out joining that two tables is there is any other way to restrict duplicates.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by vamshi g:
with out joining that two tables is there is any other way to restrict duplicates.


You can use distinct


Did you try query i suggested ?

Shailesh
vamshi g
Greenhorn

Joined: Sep 12, 2008
Posts: 26
yes query is not workin and distinct also.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by vamshi g:
yes query is not workin


What error is it giving

Shailesh
vamshi g
Greenhorn

Joined: Sep 12, 2008
Posts: 26
syntax error at or near "PARTNERID"
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

The SQL Shailesh Chandra supplied looks valid. What SQL did you run?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
vamshi g
Greenhorn

Joined: Sep 12, 2008
Posts: 26
postgres sql
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

That's a database. What SQL did you run? Can you cut and paste your query so we can see it?
vamshi g
Greenhorn

Joined: Sep 12, 2008
Posts: 26
My query is :


select a.url,a.title, b.ticket_name, c.commision , c.price_id, c.eventid from group_partner a,price b,partner_ticket_commision c where a.partnerid=3809 and c.partnerid=a.partnerid and c.price_id=b.price_id and b.evt_id=c.eventid and b.evt_id=43176.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Well that is valid SQL. Is this really what you ran?
vamshi g
Greenhorn

Joined: Sep 12, 2008
Posts: 26
yes this query only.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Are you sure? Typically, when a database reports a syntax error it reports the exact text that caused the error. I don't see PARTNERID any where in your query, though you do have a.partnerid and the like. How are you running your query? Via JDBC or directly in a SQL client?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql query returning duplicates
 
Similar Threads
problem
DB2
repeates each and everytime
n+1 select problem
Combining fields from 2 resultsets in 1 vector