More SQL: Getting a default resultset when no matches are found

seamus

Right, I'm writing up a user permissions database for part of a site.

I haven't created anything yet, but basically it will work thusly:
A user table that holds user details
A section table that hold details about each section of the site
A permission table that holds user/section/permission combos - users can have different permissions in different sections of the site.

Obviously, when accessing a section, that section will check the user's permissions for that section, and give access as required.

There'll be a "default" user for guests, which will also have permissions for each section - so guest access to each section can be opened up/restricted via the database, rather than hardcoded.

Every user that accesses the site will have a username - it's for an intranet, so user access will use NT login names. But only certain users will have specific permissions. So I need a way that if I do a query like "SELECT permission.level FROM permission, user, section where permission.user = user.id AND permission.section = 2 AND user.name = 'mydomain\joebloggs'" and it turns up empty, to return the permission for the default user.

Obviously I can test the result, and if it's empty, reissue a query for the default user, but is it possible to do this in one query rather than two?

seamus

Never mind. The live server isn't returning logon credentials anyway

I'll have to do it the old fashioned way.

BizzyC

If you were to implement it as a function, rather than a query, you could perform a check.
Not sure about about SQL but in Oracle it could be done something like this.

Function GetPermissions(P_UserName user.name%type) 
Return Ty_Refcusor As V_Refcursor Ty_Refcursor

Begin
  Open V_Refcursor For
   SELECT permission.level FROM permission, user, section 
   where permission.user = user.id AND 
   permission.section = 2 AND 
   user.name = P_UserName;

   Return V_Refcursor;

  Exception
     When No_Data_Found Then
       Open V_Refcursor For
          Select Default Values;
    Return V_Refcursor;

End GetPermissions;

Hope that makes some sense.