• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

sql query returning duplicates

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
how to change it,can you explain please.
than you.
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"vamshi vamshi",
Please check your private messages.
-DOM
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yes query is not workin and distinct also.
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
syntax error at or near "PARTNERID"
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
postgres sql
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yes this query only.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic