Problem with MySQL Query (duplicate results)

Question

I had a problem finding duplicate results in the mysql database (cocktail recipes site). Here's the setting:

Table 1: “cocktail”
[cid, c_name] (cid = unique identifier of the cocktail, c_name = name of the cocktail)

Table 2: “ingredients”:
[iid, i_name] (iid = unique identifier of the ingredient, i_name = name of the ingredient)

Table 3: "cocktail_ingredients" (reference table)
[ciid, cid, iid] (ciid = unique identifier of the string, cid = cid for cocktail, iid = ingredient iid)

Thus, one cocktail can have several rows in the cocktail_ingredients table (from 1 to many).

The setting is wonderful. The problem that I am facing right now is that there are duplicate cocktails in my database.

For example, if the cocktail_ingredients table had the following entries:

cid | n.o.
1 | 56
1 | 78
1 | 101,,
.
9 | 56
9 | 78
9 | 101

The cocktail is the same (for theoretical purposes it doesn’t matter here).

If the table "cocktail_ingredients" had another row ...

9 | 103

Then it will not be the same as the number 9 cocktail includes an additional ingredient.

Thus, mysql must perform 2 checks, firstly, that the number of ingredients is the same, and secondly, that each ingredient identifier (iid) is the same for the corresponding cocktails (cid).

Im , . , , , PHP, - , , , , .

Answer 1

, , a abd B (), , , , A, B, , . , , ;)

, - A, B. none , A B ( A B )

A, B B, , , .

CREATE VIEW ingredient_count AS
SELECT cid, count(*) as ingredients
FROM cocktail_ingredients
GROUP BY cid

CREATE VIEW shared_ingredients AS
SELECT c1.cid cid1, c2.cid cid2, count(*) as ingredients
FROM cocktail_ingredients as c1 INNER JOIN cocktail_ingredients as c2 
ON (c1.cid != c2.cid AND c1.iid = c2.iid)
GROUP BY c1.cid,c2.cid

CREATE VIEW duplicates AS
SELECT cid1,cid2
FROM (ingredient_count AS ic1 INNER JOIN shared_ingredients
        ON ic1.cid=cid1) INNER JOIN ingredient_count as ic2
     ON ic2.cid=cid2
WHERE ic1.ingredients=ic2.ingredients
AND   shared_ingredients=ic1.ingredients

, mysql , , .

Answer 2

​​, TRIGGER. , , .

, {1 | 56, 78, 101} {9 | 56, 78, 101, 103}, , .

1:

cid |  iid
----------
1   |  56

...

cid |  iid
----------
1   |  56
1   |  78
1   | 101

, 9:

cid |  iid
----------
1   |  56
1   |  78
1   | 101
9   |  56

, :

cid |  iid
----------
1   |  56
1   |  78
1   | 101
9   |  56
9   |  78

(101,103) ! 101! 101, 9 1, .

, . , .

. -:

• / ( / )

• ( ), , / ( , , select )

• If a new / updated cocktail is not duplicated, add / update the database. If