How to find duplicate set of mysql ids from bash script

Question

+---------+-------------+--------------+
|table_uid|  group_uid  | product_uid  |
+---------+-------------+--------------+
|    8901 |        5206 |          184 |
|    8902 |        5206 |          523 |
|    9194 |        5485 |          184 |
|    9195 |        5485 |          523 |
|    7438 |        1885 |          184 |
|    7439 |        1885 |          184 |
+---------+-------------+--------------+

My goal here is to show any group_uids that contain the same exact set of product_uids. So group_uid 5206 and 5485 would end up displaying while 1885 would not since it does not have the same set of product_uids. I have to accomplish this through bash script as I do not have the ability to do this in MySQL 3.23.58 (yes, it's horribly old and I hate it but not my choice). I'm trying to store the table_uid, group_uid and product_uid in an array and then compare each group_uid to see if they contain the same product_uids. Help is appreciated!

Edited to remove references to `bash`, as this is a SQL question. — Burhan Khalid, Aug 22 '13 at 12:45
Burhan, this is about reading mysql results from a bash script. My question is purely related to writing a bash script. — Walker Boh, Aug 22 '13 at 14:47
Walker why don't you edit the question and clarify it, and don't add `BASH` to the title use the tagging system instead. — Burhan Khalid, Aug 22 '13 at 14:50

score 1 · Answer 1 · edited May 23 '17 at 11:56

1

How are you displaying the table above?

You might have some success with GROUP_CONCAT Can I concatenate multiple MySQL rows into one field?

SELECT group_uid, GROUP_CONCAT(product_uid,separator ','), count(*)
FROM <tab>
GROUP BY group_uid
HAVING count(*) > 1

I'm not sure how it would order the strings as I don't have mysql at present

edited May 23 '17 at 11:56

Community

1
1

answered Aug 21 '13 at 20:48

KeepCalmAndCarryOn

8,817
2
32
47

Unfortunately, group_concat is not in mysql 3.23.58. I've thoroughly searched my options to get this done in mysql but I cannot. It would have to be done via bash script. But thanks for the suggestion – Walker Boh Aug 22 '13 at 11:11

glenn jackman · Answer 2 · 2013-08-22T13:28:50.020

Here's a bit of awk to collect the groups that belong to the same set of products:

awk '
    NR > 3 && NF == 7 {
        prod[$4] = prod[$4] $6 " "
    } 
    END {
        for (group in prod) 
            groups[prod[group]] = groups[prod[group]] group " "
        for (key in groups) 
            print key ":" groups[key]
    }
' mysql.out

184 523 :5206 5485 
184 184 :1885

If you know which set of product ids you're interested in, you can pass that in:

awk -v prod_ids="184 523" '
    NR > 3 && NF == 7 {
        prod[$4] = prod[$4] $6 " "
    } 
    END {
        for (group in prod) 
            groups[prod[group]] = groups[prod[group]] group " "
        key = prod_ids " "
        if (key in groups)
            print groups[key]
    }
' mysql.out

5206 5485

Thanks. I'll have to try this and see what results I get – Walker Boh Aug 22 '13 at 17:42 — Walker Boh, Aug 22 '13 at 17:42

How to find duplicate set of mysql ids from bash script

2 Answers2