Return value in column based on subquery result 1

[mlowe9]

I have a select query with a subquery as a column:

select name, (select count(*) from children), address, city from parents

John, 2, 123 Johnson St, Chicago
Fred, 0, 456 Jackson Ave, Chicago
Mark, 1, 789 Jordan Rd, Chicago

This is a dummied down version of what I need to do. But what I want is to instead of returning the count, I want to have the query return a value if the count is 0, and another value if it's greater than 0. Like this:

John, True, 123 Johnson St, Chicago
Fred, False, 456 Jackson Ave, Chicago
Mark, True, 789 Jordan Rd, Chicago

Is there an easy way to do this?
Thanks all.

 

[gmmastros]

This would work:

select name, 
       (select [!]Convert(Bit, [/!]count(*)[!])[/!] from children), 
       address, 
       city 
from   parents

This will return true if the count is greater than 0 and false if the count is equal to 0.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mlowe9]

That is absolutely perfect. Thanks!