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.

Any pointers would be greatly appreciated.

Thanks

score 3 · Answer 1 · answered Nov 23 '12 at 15:57

3

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

answered Nov 23 '12 at 15:57

John Woo

258,903
69
498
492

Interestingly its not much different from the APPLY OUTER above. However APPLY returns a quicker result: cross = 2.964 / Apply = 2.948 – Phill Healey Nov 23 '12 at 16:09
really? it's up to you to decide now? both cross and outer apply yields the same result? – John Woo Nov 23 '12 at 16:10
Yes, I just though it interesting to see which answer gave the quickest results, since they were all valid answers. Thanks. – Phill Healey Nov 23 '12 at 16:14
hehe try running the query thrice and calculate the average time :D – John Woo Nov 23 '12 at 16:15
Apologies, Ive realised my second select was incorrect. Please see my updated question. – Phill Healey Nov 23 '12 at 16:43

Taryn · Accepted Answer · 2012-11-23T16:46:56.853

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;

Interesting. Ive never come across APPLY before. Any chance you could explain a little as to what is going on here? Thanks. — Phill Healey, Nov 23 '12 at 15:59
@PhillHealey here is a link from MSDN -- http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx — Taryn, Nov 23 '12 at 16:00
@PhillHealey here is another question on SO with details on `APPLY` -- http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join — Taryn, Nov 23 '12 at 16:04
Interestingly its not much different from the CROSS JOIN below. However APPLY returns a quicker result: cross = 2.964 / Apply = 2.948 — Phill Healey, Nov 23 '12 at 16:09
Apologies, Ive realised my second select was incorrect. Please see my updated question. — Phill Healey, Nov 23 '12 at 16:44
Thanks. The 1st is working fine. Ive not tried the 2nd. I dont want to be cheeky, BUT.. I have a 3rd query to stick in there too. Its exactly the same as the 2nd but this counts the 'voted' field instead of 'enterNextRound'. How would this best be tackled? — Phill Healey, Nov 23 '12 at 16:57
@PhillHealey You should be able to just add it in a similar fashion, either as a field in the select list or join to it like my 3rd example — Taryn, Nov 23 '12 at 17:00

Marc · Answer 3 · 2012-11-23T16:48:30.520

1

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.

edited Nov 23 '12 at 16:48

answered Nov 23 '12 at 15:57

Marc

16,170
20
76
119

Ive never come across this configuration before. This actually throws and incorrect syntax on the ','. – Phill Healey Nov 23 '12 at 16:01
@PhillHealey I forgot the alias. Now it should work. I have tried it – Marc Nov 23 '12 at 16:06
I didnt think this would be the quickest query but it actually ties with the APPLY method given in one of the other answers. Unexpected, for such a simple contruction method. – Phill Healey Nov 23 '12 at 16:13
@PhillHealey When you do FROM 2 tables with no join, you got a Cartesian product like a cross join. I find it more easy to read like that and I am sure that it works on every db – Marc Nov 23 '12 at 16:14
Apologies, Ive realised my second select was incorrect. Please see my updated question. – Phill Healey Nov 23 '12 at 16:44

Combine 2 SQL Statements

3 Answers3