Outer join behaving like inner?

[jonpanky]

Hi,

I'm new to this forum, so I hope I have the correct place to post this...

I have a query below that is not returning the results I need:

SELECT TOP (100) PERCENT dbo.tblBlogCategories.blogCategoryName,
COUNT(dbo.tblBlogCounter.blogCounterID) AS Count,
dbo.tblBlogCategories.blogCategoryID,
dbo.tblBlogCategories.blogViewType
FROM dbo.tblBlogCategories LEFT OUTER JOIN
dbo.tblBlogCounter ON
dbo.tblBlogCategories.blogCategoryID = dbo.tblBlogCounter.blogCategoryID
WHERE (dbo.tblBlogCounter.blogCounterDate > GETDATE() - .001) OR
(dbo.tblBlogCounter.blogCounterDate > GETDATE() - .001)
GROUP BY dbo.tblBlogCategories.blogCategoryName,
dbo.tblBlogCategories.blogCategoryID, dbo.tblBlogCategories.blogViewType
HAVING (dbo.tblBlogCategories.blogViewType = 1) OR
(dbo.tblBlogCategories.blogViewType = 4)
ORDER BY dbo.tblBlogCategories.blogViewType DESC,
dbo.tblBlogCategories.blogCategoryName

I would like this query to return all results from the left table and show results from the right table that have dates newer than a certain value.

The moment I add the getdate clause it will only return matched results like an inner join, any ideas how I force it to return all the results from the left table as well?

Even though I have used the Outer Left Join criteria it still only shows results that have a join with the right table.

Many thanks for any help.

[Oralloy]

This may sound stupid, but don't you have to do the LEFT OUTER JOIN to a subselect, instead of selecting from the joined records?

Unfortunately I'm not sure of the syntax, but it would be something like

SELECT TOP (100) PERCENT
       master.blogCategoryName,
       COUNT(subQuery.blogCounterID) AS Count,
       master.blogCategoryID,
       master.blogViewType
FROM dbo.tblBlogCategories master
     LEFT OUTER JOIN
     (SELECT * FROM dbo.tblBlogCounter
      WHERE (blogCounterDate > (GETDATE() - .001)))
      subQuery
     ON master.blogCategoryID =
        subQuery.blogCategoryID
GROUP BY master.blogCategoryName,
         master.blogCategoryID,
         master.blogViewType
HAVING (master.blogViewType IN (1, 4))
ORDER BY master.blogViewType DESC,
         master.blogCategoryName ASC
 

[jonpanky]

Thanks I'll have a play and let you know how I get on.

Regards

[jonpanky]

Wow it worked, thanks so much. :)

Learnt a lot from that, did not realise that you could use subqueries in that way.

Is the perfomance of a subquery better than using views?

[Oralloy]

Super. Did my SQL work verbatim, or did you have to make changes? Since I pulled that out of the ether, so to speak, I'd like to see the working code. (I'm pretty new with subqueries myself).

As for subqueries being faster than views, I can't say. Likely it's a database server issue, and I'm sure that we have folks on bytes who are much more knowledgable than I am.

I guess the choice of which to use is mostly philosophic - Views are persistent and sharable - Subqueries are local to a bit of logic.

Overriding all of this are your role and responsibilities within the organization - Do you have the authority to create a view within the database? Is there a process you have to follow to have a DBA create a view? Or, are you stuck maintaining SQL embedded in your programming logic?

It might be that the controlling issue is maintenence - where and who is going to maintain and update this bit of SQL? Is this a one-off query, or is there critical data which will flow through this query on a regular basis?

Sorry ... I digressed ...

Thanks for the feedback and have yourself an excellent afternoon.