Mysql join removing duplicate from the result without using group by

Question

I have 3 table:

table "f" (26000 record)
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| idFascicolo      | int(11)          | NO   | PRI |         |       |
| oggetto          | varchar          | NO   |index|         |       |
+------------------+------------------+------+-----+---------+-------+

table "r" (22000 record)
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| idRichiedente    | int(11)          | NO   | PRI |         |       |
| name             | varchar          | NO   |index|         |       |
+------------------+------------------+------+-----+---------+-------+

table "fr" (32000 record)
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| id               | int(11)          | NO   | PRI |         |       |
| idFascicolo      | int(11)          | NO   |index|         |  FK   |
| idRichiedente    | int(11)          | NO   |index|         |  FK   |
+------------------+------------------+------+-----+---------+-------+

This is my SELECT:

SELECT
f.idFascicolo,
f.oggetto,
r.richiedente
FROM fr
JOIN f ON (f.idFascicolo=fr.idFascicolo)
JOIN r ON (r.idRichiedente=fr.idRichiedente)
WHERE r.name LIKE 'string%'

How can I remove the duplicate in the result (i.e. "Rossi Mario" and "Rossi Marco" can be two r.name for the f.idFascicolo ), without using GROUP BY ?

How can I remove the duplicate using WHERE clause ?!

in this post I was suggested not to use GROUP BY, but I don't know how to do it ! Thank You a lot!

data expample:

"r" data
+---------------+------------------------
| idRichiedente |   name                |
+---------------+-----------------------+
| 1             |   Rossi Mario         |
+---------------+-----------------------+
| 2             |   Rossi Marco         |
+---------------+-----------------------+

"f" data
+---------------+-----------------------+
| idFascicolo   |   oggetto             |
+---------------+-----------------------+
| 1             |   oggetto1            |
+---------------+-----------------------+
| 2             |   oggetto2..o         |
+---------------+-----------------------+

"fr" data
+---------------+-----------------------+-----------------+
| id            |   idFascicolo         |   idRichiedente |
+---------------+-----------------------+-----------------+
| 1             |   1                   | 1               |
+---------------+-----------------------+-----------------+
| 2             |   1                   | 2               |
+---------------+-----------------------+-----------------+

Maybe I am misunderstanding something but can you simply do `select distinct ... `? — Matt Cremeens, Apr 16 '19 at 14:24
@MattCremeens SELECT DISTINCT with multimple columns doesn't work, if I search 'rossi%' the result have 2 rows: first row: id:1,idFascicolo:1,idRichiedente:1 second row, id:2,idFascicolo:1,idRichiedente:2 — quattrocorde, Apr 16 '19 at 14:45
@forpas "Rossi Mario" and "Rossi Marco" are different record (with different idRichiedente) from table "r" — quattrocorde, Apr 16 '19 at 14:48
Then I usually handle this sort of thing by doing a partition, adding a row number within each partition and selecting the top row of each. — Matt Cremeens, Apr 16 '19 at 14:49
The example I had in mind is for sql server, but [here are some mysql alternatives](https://stackoverflow.com/questions/1895110/row-number-in-mysql). — Matt Cremeens, Apr 16 '19 at 15:01

Mysql join removing duplicate from the result without using group by

0 Answers0