Conditional Join (depends on table data)

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 2000 Forums

Transact-SQL (2000)

Conditional Join (depends on table data)

Author

Topic

pomela
Starting Member

15 Posts

Posted - 2006-01-10 : 04:48:45

Hey all

I want to write a join condition which depends on the DATA in the joining table, I'll explain:

Let's sat my first table is the users table which holds "UserId" (unique) and other details.
My second table holds users permissions, there may be a row for each user from the first table, IF NOT then i need to get the row with UserId=0 (general permission).

I'm trying to join these two tables with a condition that gets the permission for each user, and if not exists a row with the UserId I should join to UserId=0.

Anyone got an idea how to make this conditional join within one query.
(MSSQL 2K SP4)

Thanks.

mr_mist
Grunnio

1870 Posts

Posted - 2006-01-10 : 05:07:59

Use a left outer join from your permissions table on userid to the users table, then replace all the null entries for users with ID 0 by using a CASE statement.

-------
Moo. :)

pomela
Starting Member

15 Posts

Posted - 2006-01-10 : 05:42:07

quote:
Originally posted by mr_mist

Use a left outer join from your permissions table on userid to the users table, then replace all the null entries for users with ID 0 by using a CASE statement.

-------
Moo. :)

Thanks :)
But I think you didn't get the idea (or I didn't get yours):
I have to get ALL users and a permission for each user from the permissions table, for users with no row in permissions table i should get a row from permissions where UserId=0.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-10 : 06:21:43

quote:

I have to get ALL users and a permission for each user from the permissions table, for users with no row in permissions table i should get a row from permissions where UserId=0.

see if this helps..
this might not be the best of solution..
select a.userid, permissioncol = case when permissioncol is null
then
(select permissioncol from permission where userid =0 )
else permissioncol
end
from users a
left outer join permission b
on a.userid = b.userid

pomela
Starting Member

15 Posts

Posted - 2006-01-10 : 06:43:34

thanks

pomela
Starting Member

15 Posts

Posted - 2006-01-11 : 10:12:03

The solution i used is something like that:

SELECT *
FROM
Users U
INNER JOIN UserPermissions UP
ON (
U.UserId=UP.UserId
OR
(UP.UserId=0 and NOT EXISTS (SELECT * FROM UserPermissions WHERE UserID=U.UserID))
)

thanks for your advice :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources