sql query

I have a table Users with these int fields:
UserMainID   UserID
123               789
122               788
201               816
202               817
203               818
301               986
302               987


There is a table UserEmpMetrics with these fields
UserMainID  NumberCompleted
123                0
122                0
201                3
202                4
203                5
301                8
302                9

Also there is a CompareTable
PrimaryUserID       SecondaryUserID
788                        789
818                        816
818                        817
987                        986

What I need is this data in my temp table (PrimaryUserID from CompareTable; SecondaryUserID from CompareTable (If one PrimaryUserID
 has multiple SecondaryUserID then get SecondaryUserID from CompareTable join on UserEmpMetrics where NumberModules is
greater for a userMainID); ALSO NOTE if the numbercompleted of both userMainID are the same (or both 0), you just pick one.
Example:
##Temp:
PrimaryUserID        SecondaryUserID             UserMainID
788 (or 789)           789 (or 788)                    122 (or 123 depending on what you pick as the PrimaryUserID)
818                         817                                  203
987                        986                                  302
spiroseAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Here is my idea:

+ create a view, temp table, etc of the following:
-- CREATE VIEW CompareUPvt AS
SELECT c.PrimaryUserID AS CompareKey
     , c.PrimaryUserID AS UserID
     , u.UserMainID
     , um.NumberCompleted
FROM CompareTable c
JOIN Users u ON u.UserID = c.PrimaryUserID
JOIN UserEmpMetrics um ON um.UserMainID = u.UserMainID
UNION 
SELECT c.PrimaryUserID AS ID
     , c.SecondaryUserID AS UserID
     , u.UserMainID
     , um.NumberCompleted
FROM CompareTable c
JOIN Users u ON u.UserID = c.SecondaryUserID
JOIN UserEmpMetrics um ON um.UserMainID = u.UserMainID
;

Open in new window


OR (maybe more clear and performant)
-- CREATE VIEW CompareUPvt AS 
SELECT c.CompareKey, c.UserID, u.UserMainID, um.NumberCompleted
FROM (
   SELECT c.PrimaryUserID AS CompareKey, c.PrimaryUserID AS UserID
   FROM CompareTable c
   UNION 
   SELECT c.PrimaryUserID AS ID, c.SecondaryUserID AS UserID
   FROM CompareTable c
) c
JOIN Users u ON u.UserID = c.UserID
JOIN UserEmpMetrics um ON um.UserMainID = u.UserMainID
;

Open in new window


Once you have your CompareUPvt view (or whatever you call it, just ensure to substitute that whenever it appears from this point forward), try something like this:
SELECT MAX(CASE Ranking WHEN 1 THEN UserID END) AS PrimaryUserID
     , MAX(CASE Ranking WHEN 2 THEN UserID END) AS SecondaryUserID
     , MAX(CASE Ranking WHEN 1 THEN UserMainID END) AS UserMainID
FROM (
   -- create a view, e.g., CompareUPvtRanked, to simplify this.
   SELECT CompareKey, UserID, UserMainID, NumberCompleted
        , (SELECT COUNT(*) 
           FROM CompareUPvt c2
           WHERE c2.CompareKey = c1.CompareKey
           AND (c2.NumberCompleted > c1.NumberCompleted
           OR (c2.NumberCompleted = c1.NumberCompleted AND c2.UserID < c1.UserID))) + 1 AS Ranking
   FROM CompareUPvt c1
) AS CompareUPvtRanked
WHERE Ranking <= 2
GROUP BY CompareKey
;

Open in new window


The results I get are:
PrimaryUserID	SecondaryUserID	UserMainID
788	789	122
818	817	203
987	986	302
1098	1091	546
1011	1010	679

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Which database system is this?  For MS SQL, the syntax may look something like this if I understand everything correctly:

;WITH CTE AS (
	SELECT c.PrimaryUserID, c.SecondaryUserID
		 -- if metrics doesn't always have records, wrap as COALESCE(NumberCompleted, 0).
		 , CASE 
			  WHEN sm.NumberCompleted > pm.NumberCompleted 
				 THEN su.UserMainID 
			  ELSE pu.UserMainID
		   END AS UserMainID
		 , ROW_NUMBER() 
			  OVER(PARTITION BY c.PrimaryUserID
				   ORDER BY sm.NumberCompleted DESC) AS RN
	FROM CompareTable c
	JOIN (
	   Users pu 
	   -- if there isn't always a metrics record, change to LEFT JOIN
	   JOIN UserEmpMetrics pm ON pm.UserMainID = pu.UserMainID
	) ON pu.UserID = c.PrimaryUserID
	JOIN (
	   Users su 
	   -- if there isn't always a metrics record, change to LEFT JOIN
	   JOIN UserEmpMetrics sm ON sm.UserMainID = su.UserMainID
	) ON su.UserID = c.SecondaryUserID
)
SELECT * FROM CTE WHERE RN = 1
;

Open in new window


It is using concepts of RANKING: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

Therefore, for a better understanding, you can read the linked article which shows how to do the same thing in MySQL.  The CTE (common table expression) part, you would do via a view in MySQL or a derived table approach.

The remainder of the syntax should hold up in MySQL world as it is simply joins and case logic.

Hope that helps!

If not what you are looking for, please post back and will do my best to assist you further.
0
 
spiroseAuthor Commented:
I cannot use Common Table Expressions. Any way of modifying this?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
SharathData EngineerCommented:
What database are you working? MS SQL Server or MySQL?
0
 
spiroseAuthor Commented:
MS SQL Server
0
 
SharathData EngineerCommented:
What is the version?
0
 
spiroseAuthor Commented:
Should work on 2000/2005/2010
0
 
SharathData EngineerCommented:
Anyway, try this query.
SELECT ct.PrimaryUserID, 
       ct.SecondaryUserID, 
       u11.UserMainID 
  FROM CompareTable AS ct 
       JOIN Users AS u 
         ON ct.SecondaryUserID = u.UserID 
       JOIN UserEmpMetrics AS um 
         ON u.UserMainID = um.UserMainID 
       JOIN Users AS u11 
         ON ct.PrimaryUserID = u11.UserID 
 WHERE um.NumberCompleted = (  SELECT MAX(um1.NumberCompleted) 
                                 FROM CompareTable AS ct1 
                                      JOIN Users AS u1 
                                        ON ct1.SecondaryUserID = u1.UserID 
                                      JOIN UserEmpMetrics AS um1 
                                        ON u1.UserMainID = um1.UserMainID 
                                WHERE ct1.PrimaryUserID = ct.PrimaryUserID 
                             GROUP BY ct1.PrimaryUserID)

Open in new window

0
 
SharathData EngineerCommented:
You can also try ROW_NUMBER as mwvisa mentioned. If you don't want CTE, change it to sub-query.
SELECT PrimaryUserID, 
       SecondaryUserID, 
       UserMainID 
  FROM (SELECT ct.PrimaryUserID, 
               ct.SecondaryUserID, 
               u11.UserMainID, 
               ROW_NUMBER() 
                 OVER(PARTITION BY ct.PrimaryUserID ORDER BY um.NumberCompleted DESC) rn 
          FROM CompareTable AS ct 
               JOIN Users AS u 
                 ON ct.SecondaryUserID = u.UserID 
               JOIN UserEmpMetrics AS um 
                 ON u.UserMainID = um.UserMainID 
               JOIN Users AS u11 
                 ON ct.PrimaryUserID = u11.UserID) AS t1 
 WHERE rn = 1

Open in new window

0
 
spiroseAuthor Commented:
all three queries gives different results....
Also, what kind of joins are these?
Are we taking into account where numberCompleted for primaryuserid = numbercompleted for secondaryuserid (including where both are 0)

0
 
SharathData EngineerCommented:
>> all three queries gives different results....
See my result.
declare @Users table (UserMainID int,UserID int)
insert @Users values (123,               789)
insert @Users values (122,               788)
insert @Users values (201,               816)
insert @Users values (202,               817)
insert @Users values (203,               818)
insert @Users values (301,               986)
insert @Users values (302,               987)

declare @UserEmpMetrics table (UserMainID int,  NumberCompleted int)
insert @UserEmpMetrics values (123,                0)
insert @UserEmpMetrics values (122,                0) 
insert @UserEmpMetrics values (201,                3)
insert @UserEmpMetrics values (202,                4)
insert @UserEmpMetrics values (203,                5)
insert @UserEmpMetrics values (301,                8)
insert @UserEmpMetrics values (302,                9)

declare @CompareTable table (PrimaryUserID int, SecondaryUserID int)
insert @CompareTable values (788,                        789)
insert @CompareTable values (818,                        816)
insert @CompareTable values (818,                        817)
insert @CompareTable values (987,                        986)

SELECT ct.PrimaryUserID, 
       ct.SecondaryUserID, 
       u11.UserMainID 
  FROM @CompareTable AS ct 
       JOIN @Users AS u 
         ON ct.SecondaryUserID = u.UserID 
       JOIN @UserEmpMetrics AS um 
         ON u.UserMainID = um.UserMainID 
       JOIN @Users AS u11 
         ON ct.PrimaryUserID = u11.UserID 
 WHERE um.NumberCompleted = (  SELECT MAX(um1.NumberCompleted) 
                                 FROM @CompareTable AS ct1 
                                      JOIN @Users AS u1 
                                        ON ct1.SecondaryUserID = u1.UserID 
                                      JOIN @UserEmpMetrics AS um1 
                                        ON u1.UserMainID = um1.UserMainID 
                                WHERE ct1.PrimaryUserID = ct.PrimaryUserID 
                             GROUP BY ct1.PrimaryUserID)

/*
PrimaryUserID	SecondaryUserID	UserMainID
788	789	122
818	817	203
987	986	302
*/                             
SELECT PrimaryUserID, 
       SecondaryUserID, 
       UserMainID 
  FROM (SELECT ct.PrimaryUserID, 
               ct.SecondaryUserID, 
               u11.UserMainID, 
               ROW_NUMBER() 
                 OVER(PARTITION BY ct.PrimaryUserID ORDER BY um.NumberCompleted DESC) rn 
          FROM @CompareTable AS ct 
               JOIN @Users AS u 
                 ON ct.SecondaryUserID = u.UserID 
               JOIN @UserEmpMetrics AS um 
                 ON u.UserMainID = um.UserMainID 
               JOIN @Users AS u11 
                 ON ct.PrimaryUserID = u11.UserID) AS t1 
 WHERE rn = 1
/*
PrimaryUserID	SecondaryUserID	UserMainID
788	789	122
818	817	203
987	986	302
*/

Open in new window

0
 
spiroseAuthor Commented:

Thank you for breaking this down- also are these joins just inner joins? I want to avoid plain joins as much as possible..
0
 
spiroseAuthor Commented:
Moreover, I need to update CompareTable such that in case of an entry like this:

declare @Users table (UserMainID int,UserID int)
insert @Users values (678,               1010)
insert @Users values (679,               1011)
insert @Users values (555,               1000)
insert @Users values (556,               1091)
nsert @Users values (546,               1098)



declare @UserEmpMetrics table (UserMainID int,  NumberCompleted int)
insert @UserEmpMetrics values (678,                0)
insert @UserEmpMetrics values (679,               10)
insert @UserEmpMetrics values (555,               6)
insert @UserEmpMetrics values (556,               7)
insert @UserEmpMetrics values (546,               8)


declare @CompareTable table (PrimaryUserID int, SecondaryUserID int)
insert @CompareTable values (678,                        679)
insert @CompareTable values (555,                        556)
insert @CompareTable values (555,                        546)


In such a case, the value of PrimaryUserID and SecondaryUserID in @CompareTable needs to switch as follows:
PrimaryUserID      SecondaryUserID
679                       678
546                       555
546                       556

0
 
spiroseAuthor Commented:
EDIT:
Moreover,In our @TempTable, since in @CompareTable SecondaryUserID 556 has more numbercompleted(7) than SecondaryuserID 555(6), only this record should show up:

PrimaryUserID  secondaryuserid
546                     556
0
 
SharathData EngineerCommented:
Those are INNER JOINs.

I don't understand your last posts. You mentioned SecondaryID as PrimaryID in the expected result. Is that what you want? Why do you have 546 as PrimaryID when it is just SecondaryID?
0
 
spiroseAuthor Commented:
I think what I failed to explain is that the records are already in @CompareTable (sometimes manual entry so accuracy is not guaranteed). Our query does the verification if they are in the right place.
If that is the case in @CompareTable (records already in there), we need to switch the values of SecondaryUserID and PrimaryUserID. NumberCompleted field in @UserEmpMetrics table takes precedence and our @CompareTable gets updated accordingly (if PrimaryUserID is the record with higher NumberCompleted already, no update takes place). However, if the SecondaryUserID is the record with the highest NumberCompleted, then update takes places where the values switch.
Similarly, in @CompareTable if there are 2 (or 3 or 4...) SecondaryUserID assigned to 1 PrimaryUserID(thus 2 records in our table), our table should only show one PrimaryUserID (highest NumberCompleted) and 1 SecondaryUserID(out of the remaining, the one that has the second highest NumberCompleted next to the PrimaryUserID).
 
0
 
spiroseAuthor Commented:
I hope what I posted makes sense. THERE IS A CORRECTION IN @COMPARETABLE. I put down UserMainID instead of UserID (for PrimaryUserID and SecondaryUserID)

declare @Users table (UserMainID int,UserID int)
insert @Users values (678,               1010)
insert @Users values (679,               1011)
insert @Users values (555,               1000)
insert @Users values (556,               1091)
nsert @Users values (546,               1098)



declare @UserEmpMetrics table (UserMainID int,  NumberCompleted int)
insert @UserEmpMetrics values (678,                0)
insert @UserEmpMetrics values (679,               10)
insert @UserEmpMetrics values (555,               6)
insert @UserEmpMetrics values (556,               7)
insert @UserEmpMetrics values (546,               8)

------CORRECTION HERE!!!!!!
declare @CompareTable table (PrimaryUserID int, SecondaryUserID int)
insert @CompareTable values (1010,                        1011)
insert @CompareTable values (1000,                        1091)
insert @CompareTable values (1000,                        1098)


In such a case, the value of PrimaryUserID and SecondaryUserID in @CompareTable needs to switch as follows:
PrimaryUserID      SecondaryUserID
1011                     1010
1098                      1000
1098                      1091

Moreover,In our @TempTable, since in @CompareTable SecondaryUserID 556 has more numbercompleted(7) than SecondaryuserID 555(6), only this record should show up:

PrimaryUserID  secondaryuserid
1011                  1010
1098                    1091

Your help is greatly appreciated!
0
 
Kevin CrossChief Technology OfficerCommented:
Are there any boundaries to the solution outside of being cross compatible with versions of SQL, i.e., does this have to be a single SQL statement and/or can you create views?

I have a thought that may work, but without using a common table expression may get very nasty unless you can store the initial bit of data I need in a view, table variable or temp table.  Let me know and I can expand.  I may post here shortly when done testing it through in case you have full freedom on system.
0
 
spiroseAuthor Commented:
We can create Temporary tables and store our values there.

Basically, we need this information in our ##Temp table from the tables provided : Users, UserEmpMetrics, CompareTable

Fields needed:
PrimaryUserID SecondaryUserID PrimaryUserMainID  SecondaryUserMainID

ONLY when numbercompleted for PrimaryUserMainID < numbercompleted for SecondaryUserID.
In our CompareTable there can be multiple SecondaryUserIDs assigned to 1 PrimaryUserID. However, in our ##Temp table, we can only have one PrimaryUserid and one SecondaryUserID (highest numbercompleted field value gets PrimaryUserID and secondhighest(if the rest are same, then one random) gets SecondaryUserID )

So only for these cases:

declare @Users table (UserMainID int,UserID int)
insert @Users values (678,               1010)
insert @Users values (679,               1011)
insert @Users values (555,               1000)
insert @Users values (556,               1091)
nsert @Users values (546,               1098)



declare @UserEmpMetrics table (UserMainID int,  NumberCompleted int)
insert @UserEmpMetrics values (678,                0)
insert @UserEmpMetrics values (679,               10)
insert @UserEmpMetrics values (555,               6)
insert @UserEmpMetrics values (556,               7)
insert @UserEmpMetrics values (546,               8)

------CORRECTION HERE!!!!!!
declare @CompareTable table (PrimaryUserID int, SecondaryUserID int)
insert @CompareTable values (1010,                        1011)
insert @CompareTable values (1000,                        1091)
insert @CompareTable values (1000,                        1098)


In such a case, the value of PrimaryUserID and SecondaryUserID in @CompareTable needs to switch as follows:
PrimaryUserID      SecondaryUserID
1011                     1010
1098                      1000
1098                      1091

Moreover,In our @TempTable, since in @CompareTable SecondaryUserID 556 has more numbercompleted(7) than SecondaryuserID 555(6), only this record should show up:

PrimaryUserID  secondaryuserid
1011                  1010
1098                    1091
0
 
Kevin CrossChief Technology OfficerCommented:
The ranking piece is from my Article linked above in the section called everyone ranks the same if you want an explanation of what it is doing.

For the remainder, I am using conditional aggregates to avoid having to do another JOIN; however, if you create CompareUPvtRanked view, then you could have done a JOIN like this as final solution:
SELECT c1.UserID AS PrimaryUserID
     , c2.UserID AS SecondaryUserID
     , c1.UserMainID
FROM CompareUPvtRanked c1
JOIN CompareUPvtRanked c2 
  ON c2.CompareKey = c1.CompareKey AND c2.Ranking = c1.Ranking+1
WHERE c1.Ranking = 1
;

Open in new window


Hopefully, it is clear what I am doing in all the cases which is to unpivot the compare key table and combine the individual userid values with their respective number completed metrics, then ranking those rows with respect to their original compare table primary userid which I used as a key to combine back our final solution.  From there, you simply want to combine back rows ranked 1 and 2.

If I understood everything you wanted correctly, this should yield the UserID with most number completed as your primary userid and the secondary userid becomes the UserID with the second most.  In case of a tie, the lower UserID in sequence wins.

Hope that helps!
0
 
spiroseAuthor Commented:
I had this code (slightly modifiedfrom previous posts) but it does not update the secondaryuserids appropriately:
select-- c.primaryUserID, c.secondaryUserID,
case when sm.numbercompleted > pm.numbercompleted
        then su.UserMainID  else pu.UserMainID end as primaryUserMainID,
        --else pu.UserMainID
        --end as UserMainID,
case when sm.numbercompleted <pm.numbercompleted
then su.UserMainID else pu.UserMainID end as secondaryUserMainID,

case when sm.numbercompleted > pm.numbercompleted
then c.secondaryUserID else c.primaryUserID end as primaryUserID,
case when sm.numbercompleted > pm.numbercompleted
then c.primaryUserID else c.secondaryUserID end as secondaryUserID,
row_number() over (partition by c.primaryUserID order by sm.numbercompleted desc) as rn
into ##Temp
from CompareTable c
inner join (
Users pu
inner join
UserEmpMetrics pm on pm.UserMainID = pu.UserMainID
)
on pu.UserID = c.primaryUserID
inner join(
Users su
inner join UserEmpMetrics sm on sm.UserMainID = su.UserMainID
) on su.UserID = c.secondaryUserID
where sm.numbercompleted > pm.numbercompleted

delete from ##Temp where rn <> 1

0
 
spiroseAuthor Commented:
Thank you - let me try this and get back to you.
0
 
Kevin CrossChief Technology OfficerCommented:
PLEASE read my last two posts ( http:#a35300174 and http:#a35300269 ).  Whichever works for you, can be used later to INSERT INTO ##Temp in one shot as I believe I took care of getting the right PrimaryUserID and SecondaryUserID upfront versus having to delete one.  Also, since you said this has to work on SQL2000 ROW_NUMBER() is NOT an option.

If you want to use *all* temp tables, then anywhere I suggest creating a VIEW, just replace with a temp table, e.g., #CompareUPvt and #CompareUPvtRanked.
0
 
Kevin CrossChief Technology OfficerCommented:
*laughing* we kept missing each other, I guess.  Glad to see you saw those ... let me know if you need anything after going through those two posts. :)
0
 
spiroseAuthor Commented:

With the data I have in my tables (not just test data), 90% of my secondaryUserID values show up as NULL(where there should be values). Also, instead of UserMainID, how do we include PrimaryUserMainID and SecondaryUserMainID (corresponding to PrimaryUserID and SecondaryUserID respectively.
0
 
spiroseAuthor Commented:
For test data,
I get for the last 2 records:
Primaryuserid  Secondaryuserid  usermainid
1098                NULL                    546
1011                NULL                     679

Trying to see where I missed the mark.
0
 
Kevin CrossChief Technology OfficerCommented:
Is there anything about the data that we are unaware, i.e., is it possible for any of the data to not have a record in Users or UserEmpMetrics table?

My guess is UserEmpMetrics may not have a row and so adjustment may be this simple:
-- CREATE VIEW CompareUPvt AS 
SELECT c.CompareKey, c.UserID, u.UserMainID
     , COALESCE(um.NumberCompleted, 0) AS NumberCompleted
FROM (
   SELECT c.PrimaryUserID AS CompareKey, c.PrimaryUserID AS UserID
   FROM CompareTable c
   UNION 
   SELECT c.PrimaryUserID AS ID, c.SecondaryUserID AS UserID
   FROM CompareTable c
) c
JOIN Users u ON u.UserID = c.UserID
LEFT JOIN UserEmpMetrics um ON um.UserMainID = u.UserMainID
;

Open in new window


If that doesn't solve the issue, then please post a sample of the data that isn't showing up.

For the last piece, once we get the issues sorted, then let me know which of the directions you went with out of my suggestions and we can make the simple adjustment which in a nutshell is grabbing UserMainID from row ranked 1 as PrimaryUserMainID and thne row ranked as 2 as SecondaryUserMainID.
0
 
Kevin CrossChief Technology OfficerCommented:
If the test data you are using matches what you gave us above, then please post the QUERY you are using to get the NULLs as I didn't get any NULL rows with the test data you gave us when I tested on my system.
0
 
spiroseAuthor Commented:
Found out where I was wrong....I had (5)duplicate record sets in @Users table. I had duplicate insert into statements...this works like a charm! Thank you!!!
0
 
spiroseAuthor Commented:
Thanks
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome!
Best regards and happy coding,

Kevin
0
 
spiroseAuthor Commented:
Sorry but I have a question- I still have many records (after the coalesce update above) with NULL values for secondarymasterid when I query my tables.

My ##Temp2 (final temp table) shows:
primaryuserid    secondaryuserid     usermainid
360                    NULL                       142089

This is the situation where it occurs:
in CompareTable:
primaryuserid     secondaryuserid
360                     79280

Users table:
usermainid      userid
142089            360
No record for userid 79280

UserEmpMetrics table:
usermainid         numbercompleted
142089                0
No record for the other user

How do we eliminate records from this ##Temp2 table where secondarymasterid is null In such cases......sorry for the added trouble.
0
 
Kevin CrossChief Technology OfficerCommented:
Do you want them eliminated OR show up as:

primaryuserid    secondaryuserid     primaryusermainid    secondaryusermainid
360                    79280                       142089                     null

Also which final query did you go with?  Please post it and I will correct that one to save you having to translate it.

0
 
spiroseAuthor Commented:
Got it figured.. This one was not straightforward SQL...thanks again!
0
 
Kevin CrossChief Technology OfficerCommented:
Okay, glad you figured it out.  By the way, you could have fixed this with SQL.  Different ways depending on how you went about it which is why I wanted the exact query, but for example on the one using the GROUP BY you could add HAVING COUNT(*) = 2 to only grab rows that had two valid rows.  If you wanted the row regardless like in my last post, you could have put LEFT JOIN on the Users join.  

But anyway, the important thing is that your query is now working.  Just threw my thoughts out for future readers to understand where I was going in asking for your query.

Regards,
Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.