I am trying to accomplish this on SQL Server. The simplest table structure with data is shown below.

Table: Blog

BlogID | Title
  1    | FirstBlog
 23    | Pizza

Table: User

UserID | Name
123    | james
444    | John

Table: UserBlogMapping

UserBlogMappingID | BlogID | UserID
1                 |    1   |  123

I want to get FormID and UserBlogMappingID in one SQL query. If provided UserID is not in the mapping table, return ZERO otherwise return the valid userBlogMappingID. I am trying to run the below query but its not correct.

    Blog AS B
        LEFT JOIN BlogUserMapping AS BUM ON B.BlogID = BUM.BlogID
    (B.BlogID = 23)  -- it exists in the table
    AND BUM.userID = 444 -- it is NOT in the mmaping table but I want a ZERO return in such case 

Assumption: We can assume that the UserID provided in the WHERE clause is always a valid UserID and is present in the User table.

  • Why do you need UserBlogMappingID – paparazzo May 27 '17 at 12:29
  • @Paparazzi i need to use it in subsequent business logic. – user1451111 May 27 '17 at 12:54

You can use either ISNULL() or COALESCE() function:

    BlogID = B.BlogID, 
    BlogUserMappingID = COALESCE(BUM.BlogUserMappingID, 0)
-- ...

or the more verbose CASE expression:

--  BlogUserMappingID = CASE WHEN BUM.BlogUserMappingID IS NULL 
--                          THEN 0 ELSE BUM.BlogUserMappingID 
--                      END

The second correction that the query needs is to move the condition - regarding the table on the right side of the LEFT join - from WHERE to ON (otherwise the left join becomes an inner join):

    Blog AS B
        LEFT JOIN BlogUserMapping AS BUM ON  B.BlogID = BUM.BlogID
                                         AND BUM.userID = 444 
    B.BlogID = 23 ;

To avoid multiple rows with same BlogID, you could use an inline subquery with TOP 1, instead of the left join:

    BlogID = B.BlogID, 
    BlogUserMappingID = COALESCE(
        (SELECT TOP (1) BUM.BlogUserMappingID
         FROM BlogUserMapping AS BUM 
         WHERE B.BlogID = BUM.BlogID
           AND BUM.userID = 444
         ORDER BY BUM.BlogUserMappingID DESC
        ), 0)
    Blog AS B
    B.BlogID = 23 ;
  • What does your last statement mean? Could you please clarify. – user1451111 May 27 '17 at 10:51
  • 1
    It means that if you keep the original FROM and WHERE, the row with 23, Pizza will not appear in the results. I assume you want it to appear. – ypercubeᵀᴹ May 27 '17 at 10:53
  • The query is returning more than one rows if there are more than one records in the 'BlogUserMapping' table against a certain 'BlogID' (not just the one with matching 'BUM.UserID'). For NO matching 'BUM.UserID', it works fine. – user1451111 May 27 '17 at 11:38

You can try this:

ISNULL(count(*), 0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.