Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- If record exist in A then return record else if record exist in B return record ELSE NULL
- WITH Captains_CTE (TeamName, PlayerName)
- AS (
- SELECT TeamName, PlayerName FROM TeamPlayer WHERE IsCaptain = 1
- ),
- OldestPlayer_CTE (TeamName, PlayerName)
- AS (
- SELECT TeamName, PlayerName FROM TeamPlayer AS tp
- INNER JOIN (
- SELECT TeamName, MAX(PlayerAge) AS MaxAge
- FROM TeamPlayer
- GROUP BY TeamName
- ) AS old ON old.TeamName = tp.TeamName AND old.MaxAge = tp.PlayerAge
- )
- SELECT CASE
- WHEN Captains_CTE.TeamName IS NULL THEN OldestPlayer_CTE.TeamName
- ELSE Captains_CTE.TeamName
- END AS TeamName,
- CASE
- WHEN Captains_CTE.PlayerName IS NULL THEN OldestPlayer_CTE.PlayerName
- ELSE Captains_CTE.PlayerName
- END AS PlayerName
- FROM Captains_CTE
- FULL OUTER JOIN OldestPlayer_CTE ON Captains_CTE.TeamName = OldestPlayer_CTE.TeamName
- select a.*
- from a
- union all
- select b.*
- from b
- where not exists (select 1 from a where a.? = b.?); -- "?" is for the column that specifies whether the record exists in A
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement