Combine 2 SQL Statements

Question

I need to combine the following 2 SELECT statements, so that the result of the second statement is present as a column in every row of the first select.

SELECT MEM.Id,
   EN.artistName,
   EN.dateAdded,
   EN.voteStatus,
   ES.enterNextRound,
   ES.notified,
   ES.voted,
   GR.genre,
   ES.entrantId AS bandID
FROM recMembers AS MEM
LEFT JOIN recEntrantStatus AS ES
   ON MEM.Id = ES.judgeId
LEFT JOIN recEntrants AS EN
   ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
   ON EN.genreId = GR.Id
WHERE MEM.Id = @memberId
   AND ES.roundId = 2

SELECT COUNT(enterNextRound) 
FROM recEntrantStatus
WHERE enterNextRound = 1
  AND roundId = 2
  AND entrantId = ES.entrantId

Where 'ES.entrantId' is taken from the current row accessed in the first select.

Answer 1

You can use OUTER APPLY:

SELECT MEM.Id, 
  EN.artistName, 
  EN.dateAdded, 
  EN.voteStatus, 
  ES.enterNextRound,
  ES.notified, 
  ES.voted, 
  GR.genre, 
  ES.entrantId AS bandID, 
  src.CountEnterNextRound
FROM recMembers AS MEM
LEFT JOIN recEntrantStatus AS ES
  ON MEM.Id = ES.judgeId
LEFT JOIN recEntrants AS EN
  ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
  ON EN.genreId = GR.Id
OUTER APPLY
(
  SELECT COUNT(enterNextRound) CountEnterNextRound
  FROM recEntrantStatus
  WHERE enterNextRound = 1
    AND roundId = 2
    AND entrantId = @memberId
) src
WHERE MEM.Id = @memberId
  AND ES.roundId = 2

Based on your edit, have you tried:

SELECT MEM.Id,
   EN.artistName,
   EN.dateAdded,
   EN.voteStatus,
   ES.enterNextRound,
   ES.notified,
   ES.voted,
   GR.genre,
   ES.entrantId AS bandID,
   (SELECT COUNT(enterNextRound) 
    FROM recEntrantStatus
    WHERE enterNextRound = 1
      AND roundId = 2
      AND entrantId = ES.entrantId) CountEnterNextRound
FROM recMembers AS MEM
LEFT JOIN recEntrantStatus AS ES
   ON MEM.Id = ES.judgeId
LEFT JOIN recEntrants AS EN
   ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
   ON EN.genreId = GR.Id
WHERE MEM.Id = @memberId
   AND ES.roundId = 2

Or even:

SELECT MEM.Id,
   EN.artistName,
   EN.dateAdded,
   EN.voteStatus,
   ES.enterNextRound,
   ES.notified,
   ES.voted,
   GR.genre,
   ES.entrantId AS bandID,
   src.CountEnterNextRound
FROM recMembers AS MEM
LEFT JOIN recEntrantStatus AS ES
   ON MEM.Id = ES.judgeId
LEFT JOIN recEntrants AS EN
   ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
   ON EN.genreId = GR.Id
LEFT JOIN
(
  SELECT COUNT(enterNextRound) CountEnterNextRound, entrantId
  FROM recEntrantStatus
  WHERE enterNextRound = 1
      AND roundId = 2
  GROUP BY entrantId
) src
  ON ES.entrantId = src.entrantId
WHERE MEM.Id = @memberId
   AND ES.roundId = 2;