Delete record being duplicated in query with Inner Join

Asked

Viewed 1,020 times

1

I am making 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 manipulate JOIN very well, and this is generating a small problem.

In my current bank I am uniting the table relation 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 as 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 by my lack of experience, so I am not able to mount the QUERY correctly!

I would then like to know how to avoid this duplication of values, or how to delete all the repeated 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    |
+----+---------+----------+

When 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 list only the name of the products, but I use filters and these filters go according to these features I have in BD. And as far as I know, so I can make one WHERE ID_CARAC = '1'.

If I did a WHERE with a single condition would be fine, because I would be returning only one element based on the example, but I can use several conditions, something like:

WHERE
    ID_CARAC = 1
    OR
    ID_CARAC = 2

And that makes it at the time of foreach the product "Pen" and "Pencil" is listed more than once, and for me it would be enough to display them only once.

How can I avoid this duplication, or how can I delete repeated lines based on the product ID at select time before I even form the final array and return the result.

mysql sql database
  • 1

    Post here ALL the query you are using today, not just the WHERE clause.

  • 1

    Experiment with... GROUP BY products.id

  • Looking at the question would see the possibility of taking this ID_CARAC from the columns returned by the query and using DISTINCT not to take duplicate rows.

2 answers

2


From what you’ve described, you only use the product to make a filter, so there’s no need to show the features. So the ideal is that you do not use any kind of JOIN and yes the clause EXISTS in his WHERE:

SELECT prod.ID,
       prod.NOME
  FROM PRODUTOS prod
 WHERE EXISTS(SELECT rel.ID_CARAC
                FROM RELACAO rel
               WHERE rel.ID_PROD = prod.ID
                 AND rel.ID_CARAC IN (1, 2))

As you explained that it does not actually show any characteristic information and only filters through it, a EXISTS will have the desired effect.

EXISTS Condition

The Mysql EXISTS condition is used in Combination with a subquery and is considered "to be met" if the subquery Returns at least one Row.

In free translation:

Condition exists

The Mysql EXISTS condition is used combined with the subquery and is considered "to be met" if the subquery returns at least one record.

  • 1

    It worked PERFECTLY!! Exactly what I needed! It helped a lot brother!

0

My real goal is to list only the products, in fact I do not display on the screen these features, I list only the name of the products, but use filters...

If I got it right, what you want is to list the products that have certain features, without duplicate them in the result.

select 

distinct A.id_prod
,B.nome

from relacao as A

left join produtos as B on B.id = A.id_prod

where A.id_carac in (1,2)

SQL Fiddle: http://sqlfiddle.com/#! 9/7d3519/2

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.