This week's book giveaways are in the Jython/Python and Object-Oriented programming forums.
We're giving away four copies each of Machine Learning for Business: Using Amazon SageMaker and Jupyter and Object Design Style Guide and have the authors on-line!
See this thread and this one for details.
Win a copy of Machine Learning for Business: Using Amazon SageMaker and JupyterE this week in the Jython/Python forum
or Object Design Style Guide in the Object-Oriented programming forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
  • Knute Snortum
Sheriffs:
  • Liutauras Vilda
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Joe Ess
  • salvin francis
  • fred rosenberger

Inner Join duplicates

 
Ranch Hand
Posts: 87
1
Netbeans IDE Firefox Browser 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.
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 87
1
Netbeans IDE Firefox Browser 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 87
1
Netbeans IDE Firefox Browser 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
so you want name with the max date?
 
Kovacs Akos
Ranch Hand
Posts: 87
1
Netbeans IDE Firefox Browser 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 87
1
Netbeans IDE Firefox Browser 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 87
1
Netbeans IDE Firefox Browser 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 87
1
Netbeans IDE Firefox Browser 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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • 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!
 
Yeah. What he said. Totally. Wait. What? Sorry, I was looking at this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!