I am doing a query to my database and in this query I use an INNER JOIN to join two tables!
But I have a problem, because I do not know how to handle the JOIN very well, and this is causing a small problem.
In my current database I am joining the table with product, so the union is from N to 1, and even if it was from 1 to N the result would be the same.
The problem is that since the union is being made from N values to 1, the product is being duplicated N times, if the product in question has 10 characteristics the product will be duplicated 10 times.I believe this is caused due to my lack of experience, so I am not sure how to mount QUERY correctly!
I would then like to know how to avoid this duplication of values, or how to exclude all repeating products and leave only one.
This is a small example of the tables.
+----------------------+
| PRODUTOS |
+----+-----------------+
| ID | NOME |
+----+-----------------+
| 1 | Caneta |
+----+-----------------+
| 2 | Lapiseira |
+----+-----------------+
+----------------------+
| CARACTERISTICAS |
+----+-----------------+
| ID | VALOR |
+----+-----------------+
| 1 | AZUL |
+----+-----------------+
| 2 | NOVA |
+----+-----------------+
| 3 | BIC |
+----+-----------------+
| 4 | ESCREVE |
+----+-----------------+
| 5 | 10G |
+----+-----------------+
+-------------------------+
| RELACAO |
+----+---------+----------+
| ID | ID_PROD | ID_CARAC |
+----+---------+----------+
| 1 | 1 | 1 |
+----+---------+----------+
| 2 | 1 | 2 |
+----+---------+----------+
| 3 | 1 | 3 |
+----+---------+----------+
| 4 | 1 | 4 |
+----+---------+----------+
| 5 | 1 | 5 |
+----+---------+----------+
| 6 | 2 | 1 |
+----+---------+----------+
| 7 | 2 | 2 |
+----+---------+----------+
| 8 | 2 | 3 |
+----+---------+----------+
| 9 | 2 | 4 |
+----+---------+----------+
| 10 | 2 | 5 |
+----+---------+----------+
At the time of listing the result, the product name appears the same amount of times as the feature number it has!
+------------------------------+
| RESULTADO |
+-------+----------+-----------+
|IDPROD | ID_CARAC | NOME |
+-------+----------+-----------+
| 1 | 1 | Caneta |
+-------+----------+-----------+
| 1 | 2 | Caneta |
+-------+----------+-----------+
| 1 | 3 | Caneta |
+-------+----------+-----------+
| 1 | 4 | Caneta |
+-------+----------+-----------+
| 1 | 5 | Caneta |
+-------+----------+-----------+
| 2 | 1 | Lapiseira |
+-------+----------+-----------+
| 2 | 2 | Lapiseira |
+-------+----------+-----------+
| 2 | 3 | Lapiseira |
+-------+----------+-----------+
| 2 | 4 | Lapiseira |
+-------+----------+-----------+
| 2 | 5 | Lapiseira |
+-------+----------+-----------+
My real goal is to list only the products, in fact I do not display these features on the screen, I just want to name the products, but I use filters and these filters go according to the characteristics I have in BD. And as far as I know, so I can make a WHERE ID_CARAC = '1' .
If I did a WHERE with a single condition it would be fine, because I would be returning only one element based on the example, however I can use various conditions, something like:
WHERE
ID_CARAC = 1
OR
ID_CARAC = 2
And this causes the product "Pen" and "Pencil" to be listed more than once at the time of foreach , and for me it would be enough to display them only once.
How can I avoid this duplication, or how can I delete the repeated rows based on the product ID at the select time before even forming the final array and returning the result.