|
Subject:
|
Conditional Join (depends on table data)
|
|
Posted By:
|
pomela
|
Post Date:
|
1/10/2006 4:56:56 AM
|
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.
|
|
Reply By:
|
Lalit_Pratihari
|
Reply Date:
|
1/10/2006 10:47:58 AM
|
Hi,
I have simulated your problem and here is the scenario I have created and hope this will help.
USE NORTHWIND
CREATE TABLE USERS
(USERID INT, USERNAME VARCHAR(20))
CREATE TABLE PERMISSION
(USERID INT, PERMISION_ID INT)
INSERT INTO USERS VALUES(1, 'SACHIN')
INSERT INTO USERS VALUES(2, 'UMESH')
INSERT INTO USERS VALUES(3, 'LALIT')
INSERT INTO PERMISSION VALUES(1, 5)
INSERT INTO PERMISSION VALUES(1, 6)
SELECT ISNULL(P.USERID, 0), U.USERNAME, P.PERMISION_ID FROM USERS U LEFT JOIN PERMISSION P ON U.USERID = P.USERID
Here is the Output
-------------------
UserId UserName PermisionId
----------------------------
1 SACHIN 5
1 SACHIN 6
0 UMESH NULL
0 LALIT NULL
And UserId = 0 indicates the records where there is no entry in table permission.
Thanks,
Lalit
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
1/10/2006 11:40:39 AM
|
quote:
And UserId = 0 indicates the records where there is no entry in table permission.
But the OP requested that in this case, the permission value from the permissions table where the userID = 0 is to be used.
How about:
SELECT Users.UserID,
coalesce(Permission, (SELECT Permission
FROM Permissions
WHERE Permissions.UserID=0))
FROM Users
LEFT JOIN Permissions ON Users.UserID=Permissions.UserID
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
