CODEWITHSUNDEEP
mysqlsql
Oskar V
Oskar V

Reputation: 23

DISTINCT does not remove duplicates

Can't remove duplicates with SELECT DISTINCT for some reason.

My tables:

mysql> select * from kasutaja;
+----+---------+----------+---------------+
| id | eesnimi | perenimi | kasutaja_nimi |
+----+---------+----------+---------------+
|  1 | Juku    | Juust    | juku23        |
|  2 | Jaan    | Jaanik   | jann12        |
+----+---------+----------+---------------+

mysql> select * from riistvara;
+----+----------------+-----------+
| id | r_nimetus      | seeria_nr |
+----+----------------+-----------+
|  1 | Latitude L2100 |  33333333 |
|  2 | Latitude L2110 |  44444444 |
+----+----------------+-----------+

mysql> select * from r_paigaldus;
+-------------+--------------+----------------+
| kasutaja_id | riistvara_id | paigalduse_aeg |
+-------------+--------------+----------------+
|           1 |            1 | 2010-01-01     |
|           1 |            2 | 2010-10-01     |
|           2 |            2 | 2010-01-01     |
|           2 |            1 | 2010-10-10     |
+-------------+--------------+----------------+

The query I'm using:

SELECT DISTINCT kasutaja_nimi, eesnimi, perenimi, r_nimetus, seeria_nr, paigalduse_aeg 
FROM riistvara, kasutaja 
JOIN r_paigaldus ON id = r_paigaldus.kasutaja_id;

How the query result should end up looking like:

+---------------+---------+----------+----------------+-----------+--------------+
| kasutaja_nimi | eesnimi | perenimi | r_nimetus      | seeria_nr |paigalduse_aeg|
+---------------+---------+----------+----------------+-----------+--------------+
| jann12        | Jaan    | Jaanik   | Latitude L2100 |  33333333 |2010-10-10    |
| juku23        | Juku    | Juust    | Latitude L2110 |  44444444 |2010-10-01    |
+---------------+---------+----------+----------------+-----------+--------------+

How it looks like:

+---------------+---------+----------+----------------+-----------+----------------+
| kasutaja_nimi | eesnimi | perenimi | r_nimetus      | seeria_nr | paigalduse_aeg |
+---------------+---------+----------+----------------+-----------+----------------+
| juku23        | Juku    | Juust    | Latitude L2100 |  33333333 | 2010-01-01     |
| juku23        | Juku    | Juust    | Latitude L2110 |  44444444 | 2010-01-01     |
| juku23        | Juku    | Juust    | Latitude L2100 |  33333333 | 2010-10-01     |
| juku23        | Juku    | Juust    | Latitude L2110 |  44444444 | 2010-10-01     |
| jann12        | Jaan    | Jaanik   | Latitude L2100 |  33333333 | 2010-01-01     |
| jann12        | Jaan    | Jaanik   | Latitude L2110 |  44444444 | 2010-01-01     |
| jann12        | Jaan    | Jaanik   | Latitude L2100 |  33333333 | 2010-10-10     |
| jann12        | Jaan    | Jaanik   | Latitude L2110 |  44444444 | 2010-10-10     |
+---------------+---------+----------+----------------+-----------+----------------+

Upvotes: 1

Views: 2385

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use join correctly. Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax.

SELECT kasutaja_nimi, eesnimi, perenimi, r_nimetus, seeria_nr, paigalduse_aeg 
FROM kasutaja k JOIN
     r_paigaldu rp 
     ON k.id = rp.kasutaja_id JOIN
     riistvarar r
     ON r.id = rp.riistvara_id;

This will probably eliminate the need for select distinct. In addition, you should use table aliases and qualify all your column names.

Upvotes: 1

Filip Koblański
Filip Koblański

Reputation: 9988

First of all this query works good because the seeria_nr and paigalduse_aeg is different as you can see so DISTINCT cannot filter out them.

You can use GROUP BY to get what you want:

GROUP BY
    b.kasutaja_nimi
    ,b.eesnimi
    ,b.perenimi
    ,a.r_nimetus

this will brings to you the result that you execept - but remeber that seeria_nr and paigalduse_aeg will be showing randomly values.

Upvotes: 2

Related Questions