0

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               |
+---------------+-----------------------+-----------------+
  • "Rossi Mario" and "Rossi Marco" are not the same right? – forpas Apr 16 at 14:24
  • Maybe I am misunderstanding something but can you simply do select distinct ... ? – Matt Cremeens Apr 16 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 at 14:45
  • @forpas "Rossi Mario" and "Rossi Marco" are different record (with different idRichiedente) from table "r" – quattrocorde Apr 16 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 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.