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:
The basic gist of what I want to do is to find
AssociatedObjectIDs that have the same
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
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?