• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Inner Join duplicates

 
Kovacs Akos
Ranch Hand
Posts: 86
1
Firefox Browser Netbeans IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How can I remove the duplicates. when I use 2 inner joins. I do not know, how to filter the result and hide the duplicates.
 
Roel De Nijs
Sheriff
Posts: 10666
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use DISTINCT to list only the different values.
 
Kovacs Akos
Ranch Hand
Posts: 86
1
Firefox Browser Netbeans IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:You can use DISTINCT to list only the different values.


In this way I will lose some records. There are two columns, one for names and one for dates. I would like to filter dates and also hide the duplicated names.
 
Roel De Nijs
Sheriff
Posts: 10666
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe you should give a concrete example: some sample records from your database tables, the query you execute, the results you get and the results you want
 
Kovacs Akos
Ranch Hand
Posts: 86
1
Firefox Browser Netbeans IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The result is now looks like this:
Name Date
XY 2014-07-25
XY 2014-07-26
XY 2014-07-29
Z 2014-07-09
Z 2014-07-29

What I would like to get:
Name Date
XY 2014-07-29
Z 2014-07-29
 
Roel De Nijs
Sheriff
Posts: 10666
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
so you want name with the max date?
 
Kovacs Akos
Ranch Hand
Posts: 86
1
Firefox Browser Netbeans IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not exactly the max, today's date. I store it in a variable.
 
Roel De Nijs
Sheriff
Posts: 10666
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe first explain what you want exactly: what exactly for data do you have and what is the resultset you are aiming for.
 
Kovacs Akos
Ranch Hand
Posts: 86
1
Firefox Browser Netbeans IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please see the example below. I have made the query, that generate this output, it is already done.
 
Roel De Nijs
Sheriff
Posts: 10666
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If I don't know which query you execute, I can't suggest an edit/update. But if the query is already done, you have solved your issue
 
Roel De Nijs
Sheriff
Posts: 10666
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kovacs Akos wrote:
Roel De Nijs wrote:You can use DISTINCT to list only the different values.


In this way I will lose some records. There are two columns, one for names and one for dates. I would like to filter dates and also hide the duplicated names.

By the way, using DISTINCT will still give you exactly the same records (as in the above example), you won't lose any records. So I wonder if you know what you are doing/trying...
 
Kovacs Akos
Ranch Hand
Posts: 86
1
Firefox Browser Netbeans IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have solved this with some specific conditions in the WHERE clause.
 
Roel De Nijs
Sheriff
Posts: 10666
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Glad to hear you solved it. Little more details about the how would be appreciated by other ranchers with a similar issue.
 
Kovacs Akos
Ranch Hand
Posts: 86
1
Firefox Browser Netbeans IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:Glad to hear you solved it. Little more details about the how would be appreciated by other ranchers with a similar issue.


Filtering with more precise conditions is very important. It was the only solution.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kovacs Akos wrote:Filtering with more precise conditions is very important. It was the only solution.

That's right. "Duplicate" rows are often an indication that your selection or join conditions are incorrect or incomplete. If you find yourself using DISTINCT to eliminate duplicates, you should instead look at your query to find out why it's returning duplicates in the first place. If you use DISTINCT, you are actually telling your database to fetch a lot of data you don't really need, then "flatten" it to eliminate the unnecessary duplicates. This creates unnecessary work for the database and can cause performance problems if your query is generating a lot of duplicates, so obviously it's better to avoid this if possible.

PS: I gave you a cow for coming back here to share your learning experience!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic