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.
| Author |
Topic |
|
filyr
Starting Member
4 Posts |
 Posted - 2010-10-19 : 12:06:35
|
Hello, not sure if I posted this in the right category. Anyway, here is my question. I'm making a forum in ASP.NET. Here is the query that gets the topics for a specific category.select * from topics join (select comments.belongs_to_topic, max(datestamp) as b from comments where belongs_to_topic in (select id from topics where belongs_to_category=1 and in_subcategories='no') group by belongs_to_topic) as x on x.belongs_to_topic = topics.id order by b desc The query gets topics and orders them by the most recent comment in each topic. The problem is that is doesn't return the topics that doesn't have any comments yet (no "max(datestamp)"). How do I make "b" the datestamp from the topics table if it doesn't contain any comments ( and no "max(datestamp" from comments). Thanks! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 12:12:56
|
Try with left join and use coalesce() or isnull().
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
filyr
Starting Member
4 Posts |
Posted - 2010-10-19 : 14:05:33
|
quote:
Originally posted by webfred
Try with left join and use coalesce() or isnull().
No, you're never too old to Yak'n'Roll if you're too young to die.
Ok, not really sure how to use them though, could you tell me where to place them? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 14:24:38
|
[code]-- I don't know your table structure but maybe you can adapt this:select t.col1,t.col2,coalesce(x.b,t.datestamp) as datestampfrom topics tleft join (select comments.belongs_to_topic, max(datestamp) as b from comments where belongs_to_topic in (select id from topics where belongs_to_category=1 and in_subcategories='no') group by belongs_to_topic) as x on x.belongs_to_topic = topics.id order by coalesce(x.b,t.datestamp) desc[/code]edit: I had forgotten the order by...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-10-19 : 14:26:35
|
| [code]select * from topics t left join (select comments.belongs_to_topic, max(datestamp) as b from comments where belongs_to_topic in (select id from topics where belongs_to_category=1 and in_subcategories='no') group by belongs_to_topic) as x on x.belongs_to_topic = t.id order by coalesce(t.datestamp,b) desc[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
filyr
Starting Member
4 Posts |
Posted - 2010-10-20 : 03:48:13
|
| Thanks a lot guys, worked like a charm |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-20 : 04:02:29
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
order by - question