I have a query right now that for a lack of a better word, sucks. I'm looking for some help with respect to other ways to approach writing this query. Here are the details.

I've got a temp table build up with the following columns:

  • AssignmentSubType
  • AssignmentValue
  • OwnerUsergroupID
  • AssociatedObjectID

The basic gist of what I want to do is to find AssociatedObjectIDs that have the same AssignmentSubType, AssignmentValue, and OwnerUsergroupID. This is because there is a ton of SQL down the road that really doesn't need to be run if I've got "duplicates" (same AssignmentSubType, AssignmentValue, and OwnerUsergroupID). Once I've got my list of duplicates I run the next set of SQL stuff on one of the duplicates , when I've got back info from that I join back the rest of the duplicates for the final result set.

Example table data:

AssignmentSubType | AssignmentValue | OwnerUsergroupID | AssociatedObjectID
retailer          | Dicks           | 1                | 5
retailer          | Dicks           | 1                | 7
retailer          | Dicks           | 1                | 9

In this example I just want to do the calculations on associatedobjecid = 5 since 7 and 9 will be the exact same, and then join back in 7 and 9 at the 'end'

I've got the following query that works but is very inefficient

SELECT firstObject, f2.AssociatedObjectID 
            FROM ( 
                SELECT firstObject, dupAss AS dups 
                FROM ( 
                    SELECT min(AssociatedObjectID) AS firstObject, group_concat(concat('-',AssociatedObjectID,'-')) AS dupAss 
                    FROM ( 
                        SELECT * FROM tableFromAbove 
                    ) innerRes GROUP BY AssignmentSubType, AssignmentValue, OwnerUsergroupID 
                ) outR 
            ) outR2 
            LEFT JOIN tableFromAbove f2 
                ON outR2.dups LIKE concat('%-',f2.AssociatedObjectID,'-%') ORDER BY firstObject

this query will give me back a result set like the following

firstObject | AssociatedObjectID
5           | 7
5           | 9

like I said earlier in this post... I then use this result set to join back to the results from the rest of the queries for associatedobjectid = 5. Any thoughts on how I could restructure this so that it is more efficient?


this is josh. Try the following

SELECT  b.minID, a.AssociatedObjectID
FROM    tableFromAbove a
        LEFT JOIN
            SELECT AssociatedObjectID, AssignmentSubType, AssignmentValue, OwnerUsergroupID, MIN(AssociatedObjectID) minID
            FROM tableFromAbove 
            GROUP BY AssignmentSubType, AssignmentValue, OwnerUsergroupID
        ) b
            ON a.AssignmentSubType = b.AssignmentSubType AND a.AssignmentValue = b.AssignmentValue AND a.OwnerUsergroupID = b.OwnerUsergroupID
            WHERE minID <> a.`AssociatedObjectID`;
| improve this answer | |

Here is a simplified query for that

first get the min AssociatedObjectID as first object and then get other objects.

  t3.AssociatedObjectID first_object,
from test t
    join (
          min(t2.AssociatedObjectID) as AssociatedObjectID
        from test t2
        group by
    ) t3 
    on t.AssignmentSubType = t3.AssignmentSubType
    and t.AssignmentValue  = t3.AssignmentValue
    and t.OwnerUsergroupID  = t3.OwnerUsergroupID
    and t.AssociatedObjectID  != t3.AssociatedObjectID;
| improve this answer | |
  • that is a bit simpler than what I had but I don't think the inner join will do the trick. – Josh Apr 20 '18 at 21:55
  • @Josh Check this out sqlfiddle.com/#!9/ae1e7d/16 – DEarTh Apr 21 '18 at 4:55
  • Hi Jitendra. I did check that out but it looks like the result set is not what I'm expecting – Josh Apr 24 '18 at 13:08
  • Is it not the output you wanted ?.. sqlfiddle.com/#!9/1dbcf4/1 – DEarTh Apr 27 '18 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.