Q:

Combine 2 SQL Statements

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.
Any pointers would be greatly appreciated. 
Thanks

A:

wrap that in a subquery and use CROSS JOIN
SELECT MEM.Id,
        EN.artistName,
        EN.dateAdded,
        EN.voteStatus,
        ES.enterNextRound,
        ES.notified,
        ES.voted,
        GR.genre,
        ES.entrantId AS bandID,
        s.totalCount
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
        CROSS JOIN (
                SELECT COUNT(enterNextRound) totalCount
                FROM recEntrantStatus
                WHERE enterNextRound = 1
                        AND roundId = 2
                        AND entrantId = @memberId
                ) s
WHERE MEM.Id = @memberId
        AND ES.roundId = 2

A:

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;

