• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql query returning duplicates

 
vamshi g
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
how to change it,can you explain please.
than you.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
vamshi g
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
there is no relation price and group_partner tables am just retrieving data using partnerid and eventid
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"vamshi vamshi",
Please check your private messages.
-DOM
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Same problem, there is no join condtion between A & B so its giving duplicate records.

Shailesh
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
with out joining that two tables is there is any other way to restrict duplicates.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes query is not workin and distinct also.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by vamshi g:
yes query is not workin


What error is it giving

Shailesh
 
vamshi g
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
syntax error at or near "PARTNERID"
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The SQL Shailesh Chandra supplied looks valid. What SQL did you run?
 
vamshi g
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
postgres sql
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's a database. What SQL did you run? Can you cut and paste your query so we can see it?
 
vamshi g
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well that is valid SQL. Is this really what you ran?
 
vamshi g
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes this query only.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic