Problem with LEFT JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with LEFT JOIN

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-09-23 : 03:51:43

When I run this whole query, the "VIEWS" count turns out to be NULL. But when I run this SQL I get a count of "6"

SELECT videoid, COUNT(*) AS Views  
FROM table_video_view 
GROUP BY videoid

Here is the whole query. You can see the above SQL embeeded in this query, but when I combine it using the LEFT JOIN it ends up being NULL. What am I doing wrong?


SELECT 
    Video.*,  
    c.views,  
    Usr.Username,
    Cat.CategoryName
FROM
	[table_video] Video  

-- GET USER DATA
INNER JOIN
	[table_user] Usr
ON
	Video.userid = Usr.userid

-- GET VIEWS
LEFT JOIN 
	(SELECT videoid, COUNT(*) AS Views  
           FROM table_video_view 
           GROUP BY videoid) c  
ON
	Video.videoid = c.videoid  

-- GET CATEGORY NAME
LEFT JOIN 
	[table_Video_Category] cat  
ON
	Video.CategoryID = cat.CategoryID
WHERE
	Video.videoid=@id

Again, the problem is in the section marked "-- GET VIEWS".

Thank you.

--shawn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 04:00:10

thats because you dont have videoids corresponding to ones available in [table_video] coming from count subquery. Try below and see if it returns anything

SELECT COUNT(1)
FROM
	[table_video] Video  
INNER JOIN table_video_view  vidview
on vidview.videoid = Video.videoid

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-09-23 : 04:38:43

Thanks for the reply Visakh.

I'm confused; how would I incorporate that into my query? Somethign like this?

SELECT
Video.*,
c.views,
Usr.Username,
Cat.CategoryName
FROM
[table_video] Video

-- GET USER DATA
INNER JOIN
[table_user] Usr
ON
Video.userid = Usr.userid

-- GET VIEWS
LEFT JOIN
(SELECT COUNT(1) FROM
[table_video] Video
INNER JOIN table_video_view vidview
on vidview.videoid = Video.videoid) c
ON
Video.videoid = c.videoid

I get an error though... I think this is wrong.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 04:48:05

quote:
Originally posted by shawnmolloy

Thanks for the reply Visakh.

I'm confused; how would I incorporate that into my query? Somethign like this?

SELECT
Video.*,
c.views,
Usr.Username,
Cat.CategoryName
FROM
[table_video] Video

-- GET USER DATA
INNER JOIN
[table_user] Usr
ON
Video.userid = Usr.userid

-- GET VIEWS
LEFT JOIN
(SELECT COUNT(1) FROM
[table_video] Video
INNER JOIN table_video_view vidview
on vidview.videoid = Video.videoid) c
ON
Video.videoid = c.videoid

I get an error though... I think this is wrong.

Thanks.

what does the query i provided return?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 04:51:36

May be this is what you want

SELECT 
    Video.*,  
    ISNULL(c.views,0),  
    Usr.Username,
    Cat.CategoryName
FROM
	[table_video] Video  

-- GET USER DATA
INNER JOIN
	[table_user] Usr
ON
	Video.userid = Usr.userid

-- GET VIEWS
LEFT JOIN 
	(SELECT videoid, COUNT(*) AS Views  
           FROM table_video_view 
           GROUP BY videoid) c  
ON
	Video.videoid = c.videoid  

-- GET CATEGORY NAME
LEFT JOIN 
	[table_Video_Category] cat  
ON
	Video.CategoryID = cat.CategoryID
WHERE
	Video.videoid=@id

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-09-23 : 04:57:56

That is exactly what I was looking for :) Thank you!

So when I run this query I get the following results:


SELECT videoid, COUNT(*) AS Views  
      FROM wisetopic_video_view 
      GROUP BY videoid

VideoID Views
28 3
29 1
30 2
31 3
32 1
33 1
34 2
35 4
38 4
39 5
...

Now when I run the full query (per your modification) I still see "NULL" as all the views:


SELECT     
[Video].*, [Usr].username, [Category].categoryname,     
[Usr].DisplayName, [Comment].CommentCount, C.Views  
    
FROM           
[table_video] [Video]    
    
-- Get User    
INNER JOIN [table_user] [Usr]    
ON [Video].userid = [Usr].userid          
    
-- Get Categories
INNER JOIN [table_video_category] [Category]         
ON [Video].categoryId = [Category].categoryid       
    
 -- Get Comment Count    
LEFT JOIN        
(SELECT VideoId, COUNT (*) AS CommentCount          
	 FROM [table_Video_Comment]    
	 GROUP BY videoId) Comment           
	 ON Video.[VideoID] = Comment.[VideoId]   

-- GET VIEWS
LEFT JOIN 
(SELECT videoid, COUNT(*) AS Views  
      FROM table_video_view 
      GROUP BY videoid) c  
ON
	Video.videoid = c.videoid  

    
WHERE         
[Video].videotitle LIKE '%' + @SearchText + '%'      
OR        
[Video].description LIKE '%' + @SearchText + '%'  
  
ORDER  BY [video].dateAdded DESC

Hmmmmmm I'm stupmed :/ Please help Visakh! You are my only hope.

-- shawn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 05:13:31

i gave you solution right. the earlier query was not a part of solution. it was just to see how many records in main table exists in views.use solution posted at 09/23/2008 : 04:51:36

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-09-23 : 05:23:38

OK, I just looked at the solution you posted (the 5th post) and updated my code. Now it looks like this (but still returns NULL for the views).

Am I confused or what am I missing? Here is my complete code sample (sorry for the confusion Visakh....)


SELECT 
	Video.*,  
	ISNULL(c.views,0),
    Usr.Username,
	Cat.CategoryName
FROM
	[table_video] Video  

-- GET USER DATA
INNER JOIN [table_user] Usr
	ON Video.userid = Usr.userid

-- GET VIEWS
LEFT JOIN 
(SELECT videoid, COUNT(*) AS Views  
    FROM table_video_view 
	GROUP BY videoid) c  
	ON Video.videoid = c.videoid  

-- GET CATEGORY NAME
LEFT JOIN [table_Video_Category] cat  
	ON Video.CategoryID = cat.CategoryID

WHERE Video.videoid=@id

I'm sure this is exactly what you listed, but it still does not work. Please explain to me what I'm doing wrong here.

Thanks for your help and patience buddy.

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2008-09-23 : 05:55:06

Hello,

I solved my problem. The issue was with the VIDEO.* selector, and there being another field called "VIEWS". I changed my select list to look like this (explicit) and the problem was solved. Sorry for the mixup. My bad.



SELECT 
	[Video].VideoID,
	[Video].CategoryID,
	[Video].UserID,
	[Video].Rating,
	[Video].[FileName],
	[Video].PreviewImage,
	[Video].VideoTitle,
	[Video].Description,
	[Video].DateAdded,
	[Video].Tags, 
	[Video].Length, 
	[Usr].Username, 
	[Usr].Displayname, 
	IsNull([Comment].CommentCount,0) CommentCount, 
	cat.CategoryName,     
	IsNull(C.Views,0) Views 
FROM
	[table_video] Video

That just goes to show you its always bad practice to use the firehose (*) selector :S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources