CODEWITHSUNDEEP
asp.netsqldatabase
Rohan
Rohan

Reputation: 2935

what can be the sql query?

I have 2 tables tbl_UserRegistration and tbl_Streaks with structures like this:

tbl_UserRegistration 

               DATATYPE   LENGTH
UserName       Text         25  (PRIMARY KEY)
PhotoLink      Text        150  
DeviceToken    Integer      15
DeviceType     Integer       1

tbl_Streaks 

            DATA TYPE     LENGTH
ID          Integer         10 (PRIMARY KEY)
Player1     Text            25
Player2     Tex             25
Streaks     Integer         10

Now I want to fetch players who have highest streak with their photolink.

EDITED :

 i.e. Player1, Player2, MAX(streak), Photolink

How can I do this?

What can be sql query ?

thanks..

NOTE- Player1 and Player2 and UserName are equal..

Upvotes: 1

Views: 115

Answers (2)

Andriy M
Andriy M

Reputation: 77737

You can retrieve PhotoLink for both users by joining to tbl_UserRegistration twice. To get the pair of players who scored the most, you could sort the rows in the descending order of Streak and limit them to only one row, like this:

SELECT
  s.Player1,
  s.Player2,
  s.Streak,
  u1.PhotoLink AS PhotoLink1,
  u2.PhotoLink AS PhotoLink2
FROM tbl_Streaks s
  INNER JOIN tbl_UserRegistration u1 ON u1.UserName = s.Player1
  INNER JOIN tbl_UserRegistration u2 ON u2.UserName = s.Player2
ORDER BY s.Streak DESC
LIMIT 1

Upvotes: 1

whytheq
whytheq

Reputation: 35605

assuming Username is the same as Player1 and Player2 then you can JOIN tbl_Streaks onto tbl_UserRegistration and in the SELECT clause find the MAX() of Streaks along with Player1 and Player2

the following is in T-SQL and will pull out the max Streaks per UserName:

SELECT
    r.UserName 
    , MAX(s.Streaks) mx     
FROM 
    tbl_UserRegistration r
    INNER JOIN tbl_Streaks s ON
        r.UserName = s.Player1
GROUP BY
    r.UserName

Upvotes: 1

Related Questions