Please help me writing SQl Query

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

In this Query, you have to list pair of players with their playerID and playerName who play for the exact same teams.If a player plays for 3 teams, the other has
to play for exact same 3 teams. No less, no more. If two players currently do not play for any team, they should also be included. The query should return
(playerID1, playername1, playerID2, playerName2) with no repetition such as if player 1 info comes before player 2, there should not be another tuple with player 2
info coming before player 1.

For example if player A plays for CSK and MI, and player b plays for CSK, MI, and DD you should not get them. They both have to play for CSK, and MI and no one else.

Prerequisites:
Tables:
player(playerID: integer, playerName: string)
team(teamID: integer, teamName: string, sport: string)
plays(playerID: integer, teamID: integer)

SQL

Example data:
PLAYER    
playerID    playerName
1           Dravid
2           Sachin
3           Dhoni
4           Yuvraj
5           Ponting

TEAM      
teamID     teamName    sport
1          CSK        CRICKET
2          DD         CRICKET
3          MI         CRICKET
4          RR         CRICKET
5          KKR        CRICKET

PLAYS
playerID    TeamID
1           1
1           2
1           3
2           1
2           3
3           1
3           3

So you should get this as answer-
2, SACHIN, 3, DHONI
4, YUVRAJ, 5, PONTING

2, SACHIN, 3 DHONI is answer because both play for exclusively CSK and MI 4, Yuvraj, 5, Ponting is an answer because both players play for no teams which should be
in output.

I have written query upto

SQL

select distinct player.playerid,player.playername,plays.teamid,team.teamname,team.sport from player  left outer
join plays on player.playerid=plays.playerid
  left outer join team on plays.teamid = team.teamid where plays.teamid is null

Posted 31-Oct-14 20:26pm

Rahul Kumar kcnit

Updated 31-Oct-14 21:47pm

King Fisher

v2

Add a Solution

Comments

Laiju k 1-Nov-14 2:50am

why are given 'where plays.teamid is null'

Rahul Kumar kcnit 1-Nov-14 2:53am

To fetch the answer 4, YUVRAJ, 5, PONTING

Rahul Kumar kcnit 1-Nov-14 2:54am

I am not getting the point to fetch the condition (players who play for the exact same teams) and the answer is 2, SACHIN, 3, DHONI

Laiju k 1-Nov-14 2:56am

try my solution

Rahul Kumar kcnit 1-Nov-14 3:01am

I had tried and i have posted the result of your query

playerid playername
4 Yuvraj
5 Ponting

3 solutions

Solution 1

SQL

select distinct 
       player.playerid,player.playername,plays.teamid,team.teamname,team.sport 
from 
       player 
left outerjoin plays on player.playerid=plays.playerid
left outer join team on plays.teamid = team.teamid where plays.teamid is null and player.playerid NOT IN (select distinct playerid from plays)

Posted 31-Oct-14 20:54pm

Laiju k

Updated 31-Oct-14 21:17pm

v2

Comments

Rahul Kumar kcnit 1-Nov-14 2:57am

playerid playername teamid teamname sport
4 Yuvraj NULL NULL NULL
5 Ponting NULL NULL NULL

Its giving the answer 2, SACHIN, 3, DHONI.

Laiju k 1-Nov-14 3:35am

4 Yuvraj NULL NULL NULL
5 Ponting NULL NULL NULL

is it not giving 4 and 5 as answer

Solution 5

I'm not sure i understand you well, but, check this SqlFiddle[^].

Posted 1-Nov-14 8:14am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2014-11-01T22:49:00

If I understand you correctly, then my idea is like this:
1. Concatenate the teamid's that a player belongs to into a cell using For XML PATH()[^].
2. Then, select players from any team that have more than one player or players that do not belong to any team.
Try this:

with cte1 (playerid, teamids) as
(
Select distinct p1.playerid,
    stuff(
        (
            Select ','+ convert(varchar, ps2.teamid)
            From plays ps2
            Where ps2.playerid = ps1.playerid ORDER BY ps2.teamid ASC
            For XML PATH ('')
        ),1,1,'') AS teamids 
From player p1 left join plays ps1 on p1.playerid = ps1.playerid
)
select p3.playerid, p3.playername, c1.teamids from player p3 inner join cte1 c1 on 
p3.playerid = c1.playerid where 
(
  select count(teamids) from cte1 c2 where c1.teamids = c2.teamids
) > 1
or
c1.teamids is null
order by teamids DESC