SQL Server Performance

Conditional Join (depends on table data)

Discussion in 'T-SQL Performance Tuning for Developers' started by pomela, Jan 10, 2006.

  1. pomela New Member

    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)

  2. Adriaan New Member

    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)
  3. pomela New Member

    i hoped to find a simple solution , i guess that the only way is to query the table twice.
  4. Adriaan New Member

    Even with a UNION, this is still a single query.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  5. Adriaan New Member

    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))
  6. pomela New Member

    Thanks, I like this one <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  7. mmarovic Active Member

    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.
  8. pomela New Member

    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=':)' />
  9. ji06 New Member


    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:

    COALESCE(T2.userid, '000'),
    FROM TableA T1
    TableB T2
    ON T1.userid = T2.userid

    (I have not tested but it should work)

  10. invertedspear New Member

    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
    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.

Share This Page