CODEWITHSUNDEEP
sql-servert-sql
sajju217
sajju217

Reputation: 467

Find combination of values on same table with condition in SQL server

I have a table named PlayerScore that contains the player name and their average scores:

Id  Name     Average
1   Sakib      80
2   Tamim      70
3   Mushfiq    60
4   Sabbir     50
5   Ashraful   20
6   Aftab      40
7   Rubel      30
8   Kalu       10

I want to find their partnership combination based on a condition that, palyer whose average score is greater than 40 can not be partner with players whose score is less than 40. I tried the following query :

select a.Name,a.Average,b.Name,b.Average from ((select * from PlayerScore where Average<=40) as a inner join (select * from PlayerScore where Average<=40) as b on a.Id < b.Id) 

union 

select a.Name,a.Average,b.Name,b.Average from ((select * from PlayerScore where Average>=40) as a inner join (select * from PlayerScore where Average>=40) as b on a.Id < b.Id)

that results in :

Name    Average Name    Average
Aftab    40     Kalu    10
Aftab    40     Rubel   30
Ashraful 20     Aftab   40
Ashraful 20     Kalu    10
Ashraful 20     Rubel   30
Mushfiq  60     Aftab   40
Mushfiq  60     Sabbir  50
Rubel    30     Kalu    10
Sabbir   50     Aftab   40
Sakib    80     Aftab   40
Sakib    80     Mushfiq 60
Sakib    80     Sabbir  50
Sakib    80     Tamim   70
Tamim    70     Aftab   40
Tamim    70     Mushfiq 60
Tamim    70     Sabbir  50

Is their any solution without using UNION

Upvotes: 4

Views: 223

Answers (3)

ughai
ughai

Reputation: 9890

You can create 2 groups based on your condition and give them different values and then do a join based on the value. Something like this.

;WITH PlayerScore as
(
SELECT 1 AS Id,'Sakib' AS Name,80 AS Average
UNION ALL SELECT 2,'Tamim',70
UNION ALL SELECT 3,'Mushfiq',60
UNION ALL SELECT 4,'Sabbir',50
UNION ALL SELECT 5,'Ashraful',20
UNION ALL SELECT 6,'Aftab',40
UNION ALL SELECT 7,'Rubel',30
UNION ALL SELECT 8,'Kalu',10
),PlayerCriteria AS
(
SELECT *,CASE WHEN Average >= 40 THEN 1 ELSE 0 END joincondition
FROM PlayerScore
)

SELECT * FROM PlayerCriteria C1
INNER JOIN PlayerCriteria C2 ON C1.joincondition = C2.joincondition
AND C1.Id > C2.Id

Upvotes: 0

Arion
Arion

Reputation: 31249

Maybe you can do something like this:

SELECT
    t.*,
    t2.*
FROM
    PlayerScore AS t
    CROSS JOIN PlayerScore AS t2
WHERE t.Average>=40 AND t2.Average<40
ORDER BY t.Name

Upvotes: 1

A ツ
A ツ

Reputation: 1267

select distinct a.Name,a.Average,b.Name,b.Average 
from PlayerScore a 
join PlayerScore b 
  on a.Id < b.Id 
     and (    a.Average<=40 and b.Average<=40
           or a.Average>=40 and b.Average>=40
         )

it will likely result in the same exceution plan.

Upvotes: 2

Related Questions