SELECT boolean in result based on single row match

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT boolean in result based on single row match

Author

Topic

djpeanut
Starting Member

9 Posts

Posted - 2009-10-21 : 04:45:49

Hi,

I want to return a boolean value with each row of my result set depending on whether ONE of the matched rows fulfils my criteria.

To be more specific, I have a table of Pages, each with an id, url and (optionally) a self-referential id for a parent page. I want to select all Pages with a given parent, but I also want a column to indicate whether or not one of the returned Pages has a url that matches another value supplied in the query. The idea is that returned rows will have columns for the id, url and the boolean. Obviously the boolean value would be the same for every row.

Any help would be appreciated!

gaauspawcscwcj
Starting Member

29 Posts

Posted - 2009-10-21 : 05:10:04

select id,url,
case
when b is not null then
'True'
else
'false'
end as boo
from test

gaauspawcscwcj

djpeanut
Starting Member

9 Posts

Posted - 2009-10-21 : 06:27:14

Thanks, but I am not sure I understand. I want a query something like this pseudocode:

SELECT parentId AS selectedParentId FROM Page WHERE url = @url
SELECT id, CASE WHEN selectedParentId = parentId [of this row] THEN 'True' ELSE 'False' END AS boo FROM Page WHERE parentId = @parentId

Any idea how to do that in a single query?

Eli Kohen
Starting Member

6 Posts

Posted - 2009-10-21 : 09:31:59

This is a query that matches the the given pseudocode, but I not sure that it matches the condition in the first post.

SELECT
p.id,
CASE
WHEN ISNULL(spi.id, -1) != -1 THEN 'True' ELSE 'False'
END AS boo
FROM Page p
LEFT JOIN (SELECT id FROM Page WHERE url = @url) AS spi ON spi.id = p.parentId
WHERE parentId = @parentId

Devart team. Database managment and data access solutions.
www.devart.com

djpeanut
Starting Member

9 Posts

Posted - 2009-10-22 : 02:01:49

Thanks for the replies and for putting me on the right track. With a bit of correcting the final query was:

SELECT
p.id,
CASE
WHEN ISNULL(spi.parentId, -1) != -1 THEN 1 ELSE 0
END AS selected
FROM Page p
LEFT JOIN (SELECT parentId FROM Page WHERE url = @url) AS spi ON spi.parentId = p.parentId
WHERE p.parentId = @parentId

It does satisfy the original criteria, just using a different route to get there.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources