Advertisement
Guest User

Untitled

a guest
Aug 20th, 2019
82
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.10 KB | None | 0 0
  1. If record exist in A then return record else if record exist in B return record ELSE NULL
  2.  
  3. WITH Captains_CTE (TeamName, PlayerName)
  4. AS (
  5. SELECT TeamName, PlayerName FROM TeamPlayer WHERE IsCaptain = 1
  6. ),
  7. OldestPlayer_CTE (TeamName, PlayerName)
  8. AS (
  9. SELECT TeamName, PlayerName FROM TeamPlayer AS tp
  10. INNER JOIN (
  11. SELECT TeamName, MAX(PlayerAge) AS MaxAge
  12. FROM TeamPlayer
  13. GROUP BY TeamName
  14. ) AS old ON old.TeamName = tp.TeamName AND old.MaxAge = tp.PlayerAge
  15.  
  16. )
  17. SELECT CASE
  18. WHEN Captains_CTE.TeamName IS NULL THEN OldestPlayer_CTE.TeamName
  19. ELSE Captains_CTE.TeamName
  20. END AS TeamName,
  21. CASE
  22. WHEN Captains_CTE.PlayerName IS NULL THEN OldestPlayer_CTE.PlayerName
  23. ELSE Captains_CTE.PlayerName
  24. END AS PlayerName
  25. FROM Captains_CTE
  26. FULL OUTER JOIN OldestPlayer_CTE ON Captains_CTE.TeamName = OldestPlayer_CTE.TeamName
  27.  
  28. select a.*
  29. from a
  30. union all
  31. select b.*
  32. from b
  33. 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