How to check for duplicates in a 1 to many table

Question

I have a system which stores peoples outfits. Each outfit contains 1 to many items.

Thus my tables are like this

outfits
+-----------+--------+
| outfit_id | cus_id |
+-----------+--------+
|         1 |      5 |
|         2 |     92 |
+-----------+--------+

items
+---------+-------+-------+
| item_id | name  |  sku  |
+---------+-------+-------+
|       1 | hat   |  1111 |
|       2 | pants |  2222 |
|       3 | shirt |  3333 |
|       4 | shoes |  4444 |
+---------+-------+-------+

items_in_outfit
+--------+-----------+---------+
| ino_id | outfit_id | item_id |
+--------+-----------+---------+
|      1 |         1 |       3 |
|      2 |         1 |       2 |
|      3 |         1 |       4 |
|      4 |         2 |       1 |
|      4 |         2 |       3 |
+--------+-----------+---------+

I am given this information:

cus_id=92 wants to create another outfit with the sku's 1111 and 3333

However, this is a duplicate of outfit_id=2. So I want mysql to return me the outfit_id of 2. If its not a duplicate, return nothing. It only counts as a duplicate if the same cus_id is trying to make an outfit which has the exact same sku's already.

If cus_id=5 wants to create another outfit with the sku's 1111 and 3333. It should return nothing (as its not a duplicate) because cus_id=5 doesn't have an outfit with just 1111 and 3333

SELECT o.outfit_id FROM
outfits o JOIN
items_in_outfit iio
ON o.outfit_id = iio.outfit_id JOIN
items i
ON iio.item_id = i.item_id
WHERE cus_id = 92 AND ...

Is this a [homework question](https://meta.stackoverflow.com/questions/334822/how-do-i-ask-and-answer-homework-questions)? — Barmar, Aug 20 '20 at 06:36
@Barmar No. This is my own project. I saw mysql cannot make unique keys in 1 to many relationships. My attempt at this is in PHP, which its so slow because I concat the SKU's into a string and check if the string is the same string as the sku's provided to me. I am not sure on how to do this as its a 1 to many relationship — James, Aug 20 '20 at 06:39
See https://stackoverflow.com/questions/16704290/how-to-return-rows-that-have-the-same-column-values-in-mysql?lq=1 — Barmar, Aug 20 '20 at 06:42
@Barmar this is a 1 to many relationship table. Doing it with a 1 to 1 table is easier, which I understand. — James, Aug 20 '20 at 06:42
How are you driving this - is it a stored proc? How are you passing in the SKU's? Or are they written into a request table? — TomC, Aug 20 '20 at 06:43
I essentially using PHP and concat to the SQL statement. For example `SELECT ... FROM ... WHERE sku = ... or sku = ...`. I am concatenating `sku = ...` to the sql statement. So basically if I understand how to find an outfit with 2 items, I can just contcat the same sku's over and over to the sql statement for multiple sku's — James, Aug 20 '20 at 06:44
After you join the tables you have a 1-to-many relationship that you can use the solutions in that question to solve. — Barmar, Aug 20 '20 at 06:47
@Barmar Okay. I join the tables, but now I have multiple rows. I might have multiple different outfits from the same user. How do i figure out if its a duplicate. I don't understand how that question solves my question yet — James, Aug 20 '20 at 06:51
`AND sku IN (1111, 3333) GROUP BY o.outfit_id HAVING COUNT(*) = 2` — Barmar, Aug 20 '20 at 06:53

Gosfly · Accepted Answer · 2020-08-20T07:09:31.320

1

This may help you achieve what you are looking for :

select 
  cus_id 
from(
  select 
    cus_id, 
    group_concat(sku order by sku asc) as items
  from outfits o
  join items_in_outfit iio on o.outfit_id = iio.outfit_id
  join items i on iio.item_id = i.item_id
  group by cus_id
) T
where items = '1111,3333'

SEE DEMO HERE

EDIT : As an outfit can't contain more than 1 instance of the same item, this solution may be better for you as you don't have to order the sku id in your php before :

select 
  o.outfit_id
from 
  outfits o
  join items_in_outfit iio on o.outfit_id = iio.outfit_id
  join items i on iio.item_id = i.item_id
where sku IN (1111, 3333) 
group by o.outfit_id having COUNT(*) = 2

SEE SECOND DEMO HERE

edited Aug 20 '20 at 07:09

answered Aug 20 '20 at 06:58

Gosfly

1,240
1
8
14

Yes of course, just concat them in the right order in your php – Gosfly Aug 20 '20 at 07:00
Oh true. You can do that too – James Aug 20 '20 at 07:02
Also there may be a better solution as barmar said, however it depends on one thing, can an outfit_id have the same item_id multiple times ? – Gosfly Aug 20 '20 at 07:04
No. For example and `outfit_id` can only have 1 instance of an `item_id`. So this outfit is NOT possible `1111,1111,3333`. However, this `outfit_id` IS possible, `1111,3333`. Is there a better solution? – James Aug 20 '20 at 07:07
See my edit above, this may be a little better for your case – Gosfly Aug 20 '20 at 07:10

How to check for duplicates in a 1 to many table

1 Answers1