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.

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

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:
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com