-4

I have tried to implement it with this tutorial: http://www.w3schools.com/sql/sql_join_inner.asp It is working, but I get duplicate entries. I have 3 tables and I would like to get only 2 columns. One for name and one for date. For 1 name is more date in the table. I would like to get only 1 date per name, only today.

EDIT: 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
SQLChao
  • 7,709
  • 1
  • 17
  • 32
user007
  • 1
  • 1
  • Have you tried using grouping? I recommend looking up `GROUP BY`: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html – drew_w Jul 29 '14 at 18:26
  • 2
    I guess we can't guess what is on your table and what did you tried to get those duplicates right? So put your tables structure on your question plus your sql command so we can see it right here. Links can broken and your question will be useless – Jorge Campos Jul 29 '14 at 18:26
  • Grouping is good, but I "lose" some records so, sometimes that one, that I need. – user007 Jul 29 '14 at 18:30
  • 2
    SO is not a fan of w3. Not sure how w3 feels about SO – Strawberry Jul 29 '14 at 18:47
  • Do you only want the records from one date? Use the WHERE clause. Do you want the latest? Use the max() + GROUP BY. Do you want one that depends on a complex logic (You might need to do some filtering in the application logic. Please put the full table structures, the exact question you want answered and the SQL statements. – J-Rou Jul 29 '14 at 18:54
  • Your question is unclear. There are no duplicates as you said each name can have more than one date. If you only want to see today's date why not do `WHERE date = '2014-07-29`. Of if you want to see the one row per name with the most recent date then `select name, max(date) from blah blah blah group by name` – SQLChao Jul 29 '14 at 18:54
  • JChao: There are also another columns in this table. If I maximize the date, I will lose again rows from other columns, I think. What does SO means? – user007 Jul 29 '14 at 19:16

1 Answers1

0

  1. To get only the name and the date you need to use the SELECT statement (http://www.w3schools.com/sql/sql_select.asp)

  2. INNER JOIN doesn't remove duplicates. To do that you need to use the DISTINCT statement (http://www.w3schools.com/sql/sql_distinct.asp)

Example of your problem (NOTE: Tables are fake and might not make sense, they're just for the example)

TABLE1         TABLE2
|id | date |   |id | name | age |
| 1 | 2010 |   | 1 | mike |  5  |
| 2 | 2010 |   | 2 | mike |  6  |

Since you have 2 people called mike that have the same date, if you join the tables you will get:

enter code here

SELECT t1.id, t1.date, t2.name, t2.age
FROM TABLE1 as t1
INNER JOIN TABLE2 as t2 ON t1.id = t2.id

TABLE JOINED
|id | date | name | age |
| 1 | 2010 | mike |  5  |
| 2 | 2010 | mike |  6  |

If you only select the date and the name, you'll get the same lines, but without the age and the id.

SELECT t1.id, t1.date, t2.name, t2.age
FROM TABLE1 as t1
INNER JOIN TABLE2 as t2 ON t1.id = t2.id

TABLE JOINED / FILTERED
| date | name |
| 2010 | mike |
| 2010 | mike |

You can see the repeated rows.

The distinct statement is for that.

The w3schools tutorial can be a little hard to follow (it's not organized in clear steps). I would recommend googling for a different one.

=========

EDIT: You changed the question.

For that you need to use GROP BY and MAX (http://www.w3schools.com/sql/sql_groupby.asp) (http://www.w3schools.com/sql/sql_func_max.asp)

J-Rou
  • 2,216
  • 8
  • 32
  • 39
  • I'm amusing you are not referring to the WERE statement to filter out rows. (http://www.w3schools.com/sql/sql_where.asp) If you are referring to that, please, read a complete tutorial before asking anything else. – J-Rou Jul 29 '14 at 18:49
  • There are also another columns in this table. If I maximize the date, I will lose again rows from other columns, I think. – user007 Jul 29 '14 at 19:18
  • What exactly do you want?? What's the question you want answered with the query?? Also, What's the schema of the tables?? I think you might be i the following case: http://stackoverflow.com/questions/17814961/select-distinct-rows-whilst-grouping-by-max-value – J-Rou Jul 29 '14 at 20:59