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 |
|
kensai
Posting Yak Master
172 Posts |
 Posted - 2002-08-09 : 13:04:21
|
I need two join two recordset. I have a table named Forums and a table named Topics. Forums' ID column is foreign key with Topics' Forum column.The first recordet includes everything from FORUMS table and returns 3 columns with 19 rows. (select * from FORUMS)The second recordset is kinda different. I need to select latest post for every forum. For example: "select top 1 ID from TOPICS where Forum='1' order by Date desc" gets last post for Forum #1. Like this, I need to get last post for every forum.So, I need to join such two records but since I'm not that good at T-SQL I don't know how I can do it. Can you give me an idea?Here are the tables:FORUMSID Name SortOrder----------- ----------- --------------1 Internet 12 Software 3.....19 Programming 6TOPICSID Forum Topic Date----------- ---------- --------- ----------1 1 blabla.. 01/01/2002 16:502 1 blabla.. 01/01/2002 16:513 4 blabla.. 01/01/2002 16:404 4 blabla.. 01/01/2002 17:305 5 blabla.. 01/01/2002 15:556 5 blabla.. 01/01/2002 19:107 2 blabla.. 01/01/2002 11:20 |
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-08-09 : 14:02:15
|
| I think you are looking for something like this:SELECT f.*, t.*FROM forums f INNER JOIN topics t ON f.id = t.forumWHERE t.date = (SELECT MAX(date) FROM topics WHERE forum = f.id) |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-09 : 14:59:26
|
| It's like deja vu all over again.I think you'll find that a derived table in your from clause will run a tiny bit faster...Jay White{0} |
|
|
|
|
|
|
Record joining