CODEWITHSUNDEEP
sqljoin
yogi
yogi

Reputation: 19591

Eliminate sub queries from this Query

I have these tables

User

User_Id int PK, User_Name varchar(50)

Page

Page_Id int PK, Page_Name varchar(50)

Permission

Permission_Id int PK , User_Id int FK, Page_Id int FK, 
Read bit, Write bit, Delete bit

Requirements:

  1. Query to select all from Page plus Read, Write, Delete from Permission of that page according to provided @User_Id.
  2. If @User_Id isn't passed all Pages should be selected with null in Read, Write, Delete.
  3. If provided @User_Id doesn't exists in Permission then Read, Write, Delete should be selected as null.
  4. There may be some case when permission would have records for some but not all pages associated with a user, In that case all records of pages should come up and for those pages which doesn't have associated user_id there should be null in Read, Write, Delete columns.

What I have done is this

declare @User_Id int=null; 

    SELECT 
     page.Page_Name,
     page.Page_Id,
     isnull((select Read from Permission where
         (@User_Id is null OR User_Id = @User_Id ) and 
         (Permission.Page_Id = Page.Page_Id)),0)
         as Read,
     isnull((select Write from Permission where 
         (@User_Id is null OR User_Id = @User_Id) and 
         (Permission.Page_Id = Page.Page_Id)),0) 
         as Write,
     isnull((select Delete from Permission where 
         (@User_Id is null OR User_Id = @User_Id) and 
         (Permission.Page_Id = Page.Page_Id)),0) 
         as Delete,
     isnull((select Export from Permission where 
         (@User_Id is null OR User_Id = @User_Id) and 
         (Permission.Page_Id = Page.Page_Id)), 0) 
         as Export  
    FROM Page

This query works fine and fulfill all requirements but as you can see it has sub queries which I want to eliminate, one option could be sql functions but I want to know if there's some other way to solve this.

Upvotes: 1

Views: 64

Answers (1)

Tobsey
Tobsey

Reputation: 3400

declare @User_Id int=null; 

SELECT 
    page.Page_Name,
    page.Page_Id,
    ISNULL(Read,0) AS Read
    ISNULL(Write,0) AS Write,
    ISNULL(Delete,0) AS Delete,
    ISNULL(Export,0) AS Export
FROM 
    Page
    LEFT JOIN Permission ON Page.Page_ID = Permission.Page_ID AND Permission.User_ID = @User_ID

If you join on the @userID parameter too then when it is null it will not match with any of the rows in Permissions, and therefore your values will be NULL

Upvotes: 2

Related Questions