MySQL Query problem (duplicate results)?

You may recall from a distant math class that the definition of set equality is that both A abd B are subsets of one another (non-strict) so just create a view or procedure that checks if every thin that is in A is also in B, then check the two cocktails are both subsets of one another. This is far from a complete answer, but it may be enough to get you going ;) it will probably be easier to do the negation - find an ingredient in A that is not in B. None exist, then A must be a strict subset of B (assuming A and B can't both be empty) Alternatively do a count of each ingredient in A, each ingredient ion B and each ingredient in A and B then if they are equal they are equivalent cocktails 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 Note this may be much faster in mysql with subselects with sensible where clauses rather than views, but this is easier to understand.

Thanks a ton, looks good, and my Math class memories are slowly coming back to me. I'll have a go later, fingers crossed. – Nick Wilkins Jan 12 at 13:38 I think it works, but way to slow for my database size (cocktail table hold 3000 records, cocktail_ingredients holds over 12000, and ingredients holds around 650).

Im working on a PHP solution, but again its tricky .... – Nick Wilkins Jan 12 at 14:59 @Nick, see my note at the bottom - you'll be able to speed this up a lot by using sub-queries instead of the views (with appropriate where clauses) I wrote that for readability not speed. In my experience this kind of query can go from 30+ seconds to a small fraction of a second if done right. – tobyodavies Jan 12 at 23:20.

You can impose such checking using TRIGGER. But, yet there is a conceptual problem. Say, you have two cocktails {1 | 56, 78, 101} and {9 | 56, 78, 101, 103} and also assume that you have implemented the check.

Now, you are inserting data for 1: cid | iid ---------- 1 | 56 Then, add rest two ingredients... cid | iid ---------- 1 | 56 1 | 78 1 | 101 Fine, now you start adding 9: cid | iid ---------- 1 | 56 1 | 78 1 | 101 9 | 56 You have three more ingredients, so continue adding them: cid | iid ---------- 1 | 56 1 | 78 1 | 101 9 | 56 9 | 78 Two more remaining (101,103) But alas! You cannot add 101! If you try to add 101, then 9 would become identical to 1, which your trigger will prevent you from adding.

When a cocktail is subset of another, you have to add the subset later. I hope I could make you understand this. You should not put any restriction in database.

What I would do in my web application is: In the cocktail entry/update interface, I would take user input (and not yet insert/update in DB) When user clicks the save button (I would add a save button), check if the new/updated cocktail becomes copy of another (May be I would write a stored procedure, but it can be found using a select query only) If the new/updated cocktail is not duplicate of another, insert/update database. If.

1 for You should not put any restriction in database - this is the worst advice I have ever heard. – tobyodavies Jan 12 at 13:12 Its not for restrictions for adding, just for cleansing up existing entries. I may put in some notification that it "may" be the same, but wouldn't stop it by default.

– Nick Wilkins Jan 12 at 13:37 @tobyodevis: plz read the entie answer. You will find that I said db restriction can be implemented. But in the end, I suggested something else.

Anyways, using trigger is a very common practise by oracle dba's. Though its the worst thing you have heard – Sarwar Erfan Jan 12 at 14:00 nowhere was it suggested that he wanted to prevent the addition of 'identical' cocktails - he made it pretty obvious to me with the 'theoretical purposes' comment that he just wants a list he can hand check and merge as appropriate. As such an SQL solution is preferable to talking about triggers and then not using them... and "You should not put any restriction in the database" is a bad idea, perhaps this restriction is a bad one, but in general they are pretty useful – tobyodavies Jan 12 at 23:29.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.