asked on

SQL Query - Need help joining tables where null values exist

I'm developing a blog system and have the following tables i.e.

CREATE TABLE [dbo].[Users](
      [UserId] [int] NULL,
      [Name] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[BlogSubscription](
      [BlogSubscriptionId] [int] NOT NULL,
      [SubscriptionName] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

-- MANY TO MANY
CREATE TABLE [dbo].[UserBlogSubscription](
      [UserBlogSubscriptionId] [int] NOT NULL,
      [BlogSubscriptionId] [int] NULL,
      [UserId] [int] NULL
) ON [PRIMARY]

I want to return all the BlogSubscription names and include a column showing if a specfici user e.g. Joe is subscribed to it e.g.

BlogSubscriptionId |       BlogSubscriptionName | subscribed
1                  Personal             True
2                  Shopping             False
3                  Something             True

At the moment i'm doing this in two seperate calls i.e.

- Get all BlogSubscription
- Get all UserBlogSubscription where UserId = 1
And then i'm physically matching them in code

Is there an efficent way to do this in one SQL call?

Guy Hengel [angelIII / a3]

that is a left join

Ashish Patel

Select A.BlogSubscriptionId, A.SubscriptionName, Case When B.UserID Is Null Then False Else True End as subscribed
From BlogSubscription A
Left Join UserBlogSubscription B On A.BlogSubscriptionId = B.BlogSubscriptionId
Where B.UserID = 1

Answer_Me

Try this:

SELECT BlogSubscription.BlogSubscriptionId,BlogSubscription.SubscriptionName,CASE WHEN UserBlogSubscription.UserBlogSubscriptionId IS NULL THEN 'false' ELSE 'true' END AS 'Subscribed'
FROM BlogSubscription
LEFT JOIN UserBlogSubscription ON BlogSubscription.BlogSubscriptionId=UserBlogSubscription.BlogSubscriptionId
JOIN Users ON Users.UserId = UserBlogSubscription.UserID

Open in new window

paulCardiff

ASKER

Thanks answer_me and asvforce:

I started of thinking something simular but the problem i have is

This wont ever display False i.e. we're saying Where B.UserID = 1 but at the same time saying display 'false 'when null

Or is this me overcomplicting a simple things as usual ;-)

Ashish Patel

Sorry, that should be 'False'...you are right. Minor mistakes do happen.

Answer_Me

I'll explain it once again correct me if I am wrong. In UserBlogSubscription you have entries for users who have subscribed to some of the blogs listed in BlogSubscription.
So if there is a blog in BlogSubscription whose entry is missing for user in UserBlogSubscription that means that the user is not subscribed to that blog.

To ensure that subscription status for such case is listed you need to have a left join. To depict the exact subscription status you need to check against the null vaue. If null implies not subscribed.

I hope this makes everything clear.

Ashish Patel

Try it once and see if its displaying false or not. Did you check the query? Please replace False with 'False' and True with 'True' and try. Let us know what you are getting.

Answer_Me

For user id check use this query
SELECT BlogSubscription.BlogSubscriptionId,BlogSubscription.SubscriptionName,CASE WHEN UserBlogSubscription.UserBlogSubscriptionId IS NULL THEN 'false' ELSE 'true' END AS 'Subscribed'
FROM BlogSubscription
LEFT JOIN UserBlogSubscription ON BlogSubscription.BlogSubscriptionId=UserBlogSubscription.BlogSubscriptionId
JOIN Users ON Users.UserId = UserBlogSubscription.UserID and Users.UserID =1

paulCardiff

ASKER

Sorry asvforce i wasn't refereing the quotes but let me explain whats happening i.e.
i've put the following test data in i.e. please see sample when i run both answer_me and asvforce get

2      Shopping      true

Whereas i need
1      Personal False
2      Shopping True
3      .net False

Users
1	Joe
2	Paul
3	Bev
 
 
BlogSubscription
1	Personal
2	Shopping
3	.net
 
UserBlogSubscription i.e. 
UserBlogSubscriptionId | BlogSubscriptionId  | UserId
1	2	1

Open in new window

Answer_Me

Try this:
SELECT BlogSubscription.BlogSubscriptionId,BlogSubscription.SubscriptionName,CASE WHEN UserBlogSubscription.UserBlogSubscriptionId IS NULL THEN 'false' ELSE 'true' END AS 'Subscribed'
FROM BlogSubscription
LEFT JOIN UserBlogSubscription ON BlogSubscription.BlogSubscriptionId=UserBlogSubscription.BlogSubscriptionId
LEFT JOIN Users ON Users.UserId = UserBlogSubscription.UserID and Users.UserID =1

paulCardiff

ASKER

Hi Answer me,
Yes this half works i.e. it brings the result i need i.e.

1      Personal      false
2      Shopping      true
3      .net      false

But its not user specific i..e. if i add user id 3 to BlogSubscriptionId 3 so that the UserBlogSuscription now looks like

1      2      1
3      3      3

Using your recommednation I get which i wrong as it needs to be specific to user 1 i.e .user 1 is not susbribed to 3

1      Personal      false
2      Shopping      true
3      .net      true

ASKER CERTIFIED SOLUTION

Answer_Me

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

paulCardiff

ASKER

Thanks everyone - works like a charm