Conditional Join (depends on table data) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Conditional Join (depends on table data)

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.
Use a UNION like so … select u.*, p.*
from users u
inner join permissions p
on u.userid = p.userid union all
select u.*, p.*
from users u,
(select * from permissions p1 where p1.userid = 0) p
where not exists (select * from permissions p2 where p2.userid = u.userid)
thanks.
i hoped to find a simple solution , i guess that the only way is to query the table twice.

Even with a UNION, this is still a single query.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
Another option would be to use a non-join, like so: select u.*, p.*
from users u, permissions p
where (u.userid = p.userid)
or (p.userid = 0 and not exists (select * from permissions p1 where p1.userid = u.userid))
Thanks, I like this one <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
It looks like outer join description: declare @userPermission <userPermission data type> select @userPermission = p.userPermission
from permissions p
where p.userID = 0 select u.*, isNull(p.userPermission, @userPermission)
from users u
left join permissions p on p.userID = u.userID
This is solution I prefer, but maybe it can also be done by just one select: select u.*, isNull(p.userPermission, p0.userPermissions)
from users u
left join permissions p on p.userID = u.userID
join permissions p0 on p0.userID = 0
I have never tried such join, not sure it would work. Another untested possibility is: select u.*, isNull(p.userPermission, (select p0.userPermission
from persmissions p0
where p0.userID = 0)
from users u
left join permissions p on p.userID = u.userID
You can also make udf from select inside isNull function call.
The solution i used is something like that:<br /><br />SELECT *<br />FROM<br />Users U<br />INNER JOIN UserPermissions UP<br />ON (<br />U.UserId=UP.UserId<br />OR<br />(UP.UserId=0 and NOT EXISTS (SELECT * FROM UserPermissions WHERE UserID=U.UserID))<br />)<br /><br />thanks for your advice <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
Hi, I see for your example that you have this situation: TableA (userid, name) 001 John
002 Karen
003 pomela TableB (userid, permissionid) 001 A
002 B
so the result would be 001 John
002 Karen
000 pomela
so more or less the query would be like: SELECT
COALESCE(T2.userid, ‘000’),
T1.name
FROM TableA T1
LEFT OUTER JOIN
TableB T2
ON T1.userid = T2.userid (I have not tested but it should work)
This is an old post I found through google when researching a similar problem and I have a solution that may help someone having a slightly different type of issue. I’m sure it could solve the existing issue, but I’m trying to help people people searching to the issue I was having [;)]
I want to change which columns I join on depending on the data in the table,
SELECT *
FROM TableA a, TableB b
WHERE
CASE WHEN a.col1 > x THEN a.col1 ELSE a.col2 END
=
CASE WHEN a.col1 > x THEN b.col1 ELSE b.col2 END
[Rest of where statement]
This has the effect of joing col1 from both tables when the value in tableA col 1 is greater than 100, otherwise it joins the tables on col2. Hope this post helped someone.

]]>