CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Phill Healey
Phill Healey

Reputation: 3180

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.

Upvotes: 1

Views: 357

Answers (3)

Marc
Marc

Reputation: 16512

How about

SELECT * 
FROM
(
    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
) x
,
(
    SELECT COUNT(enterNextRound) as Total
    FROM recEntrantStatus
    WHERE enterNextRound = 1
    AND roundId = 2
    AND entrantId = @memberId
) y

When you do FROM 2 tables with no join, you got a Cartesian product like a cross join. So it should be the same execution time.

This is the implicit way to write it.

Upvotes: 1

Taryn
Taryn

Reputation: 247620

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;

Upvotes: 3

John Woo
John Woo

Reputation: 263683

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

Upvotes: 3

Related Questions