Anyway to perform this test in a single Query?

Question

Anyway to perform this test in a single Query?

Trystan Hughes

SSCrazy

Points: 2109
More actions
September 25, 2006 at 9:12 am

#170289

Hi all,
I have the following two queries where I need to COUNT values so that I can compare them, but am wondering whether it is possible for me to somehow does this in a single query?
Query 1
SELECT COUNT(*)
FROM tbl_NSP_QuestionTree AS Q
WHERE (Q.ParentUID = 510095)
Query 2
SELECT COUNT(*)
FROM tbl_NSP_QuestionTree AS Q
RIGHT OUTER JOIN tbl_NSP_Answer AS A ON (Q.QuestionTreeUID = A.QuestionTreeUID)
WHERE (Q.ParentUID = 510095)
AND (A.InspectionUID LIKE '00076_0000001481')
I can't really do Sub-Selects apart from using them within the IN clause (SQL Mobile Everywhere )
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Derived table???

You can save the first result into a variable then use the variable in the select... or maybe use a derived table then cross join to the results of the right join!?

Sergiy SSC Guru Points: 110208 More actions · Answer 2

IF (

SELECT COUNT(Q.QuestionTreeUID ) - COUNT(A.QuestionTreeUID)

FROM tbl_NSP_QuestionTree AS Q

RIGHT OUTER JOIN tbl_NSP_Answer AS A ON (Q.QuestionTreeUID = A.QuestionTreeUID)

WHERE (Q.ParentUID = 510095)

AND (A.InspectionUID LIKE '00076_0000001481')

) <> 0

_____________
Code for TallyGenerator

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

SELECT COUNT(*) AS MainCount,

Sum(CASE WHEN A.InspectionUID LIKE '00076_0000001481'

THEN 1 ELSE 0 END) AS SubCount

FROM tbl_NSP_QuestionTree AS Q LEFT OUTER JOIN

tbl_NSP_Answer AS A ON (Q.QuestionTreeUID = A.QuestionTreeUID)

WHERE (Q.ParentUID = 510095)

-Eddie

Eddie Wuerch
MCM: SQL

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 4

Thanks for the replies guys.

I will try these out, and then get back to you.

Thanks again.