We help IT Professionals succeed at work.

SQL Query - Need help joining tables where null values exist

264 Views
Last Modified: 2010-03-19
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?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
that is a left join
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

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

Author

Commented:
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 ;-)
Sorry, that should be 'False'...you are right. Minor mistakes do happen.
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.
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.
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

Author

Commented:
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

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

Author

Commented:
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks everyone - works like a charm
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.