Adding a field with true of false for output

Question

Post reply

Adding a field with true of false for output

krizinbizz

Newbie

Points: 3
More actions
March 3, 2003 at 3:37 am

#80841
Hi,
I hope this is the right forum for this question. I have two tables, one called UserGroups and another called UserGroupRelations.
Usergroups looks something like:
```
UserGroupID | UserGroup

----------------------------

1           | Test

2           | More Testing

3           | Another group

4           | Hello

5           | World
```
And UserGroupRelations looks something like:
```
UserGroupID | UserID

----------------------------

3           | 5

5           | 5
```
So, here's the point. I want to make a multiple selectbox in HTML where it lists out all the UserGroups but have the groups the user is a member of selected.
What I need is the SQL to output something that i can check for in ASP to know if it should be selected or not. i.e:
```
UserGroupID | UserGroup      | Selected

-------------------------------------

1           | Test           | False

2           | More testing   | False

3           | Another group  | True

4           | Hello          | False

5           | World          | True
```
Of course I could do this using two queries in ASP and then match them against eachother, but if I could have it returned like over just using one stored procedure that would be of good help.
Anyone got an idea how I can do this?
Edited by - krizinbizz on 03/03/2003 04:04:51 AM

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

I think you are looking for something like this

SELECT

UG.UserGroupID,

UG.UserGroup,

CAST((CASE WHEN UserID IS NULL THEN 0 ELSE 1 END), AS BIT) Selected

FROM

dbname.dbo.UserGroups UG

LEFT JOIN

dbname.dbo.UserGroupRelations UGR

ON

UG.UserGroupID = UGR.UserGroupID

BIT fields are seen as true/false values in ASP and should do the trick for you.

krizinbizz Newbie Points: 3 More actions · Answer 2

Thanks, that's a lot closer then I ever got. However, it returns selected = 1 on all usergroups instead of just two of them.

Added a DISTINCT as well, so now it looks like



SELECT DISTINCT

UG.UserGroupID,

UG.UserGroup,

CAST((CASE WHEN UserID IS NULL THEN 0 ELSE 1 END) AS BIT) Selected

FROM UserGroups UG

LEFT JOIN UserGroupRelations UGR

ON UG.UserGroupID = UGR.UserGroupID

krizinbizz Newbie Points: 3 More actions · Answer 3

Oops..

I added WHERE UserID = 3 after ON UG.UserGroupID = UGR.UserGroupID and it worked just fine.

Thank you very much! 🙂