Database Discussion - inner join

Print Page | Close Window

inner join

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=7883
Printed Date: 30 March 2026 at 12:09pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: inner join

Posted By: Badaboem
Subject: inner join
Date Posted: 05 December 2003 at 9:18am

I've read some stuff about inner join on aspin, but I still can't figure out something.

I'm trying to adjust the latest forum posts mod so it can link to the last post of a thread.

I've added:

Dim inglastEntryMessageID

lngLastEntryMeassgeID = CLInt(rsLatestPosts("Thread_ID"))

But now I need to adjust:


 strSQL   = "SELECT TOP 12 tblTopic.Topic_ID, tblTopic.Subject, tblForum.Forum_ID, Last(tblAuthor.Username) AS LastOfUsername, tblForum.[Read], tblThread.[Thread_ID]"
 strSQL = strSQL & " FROM (tblForum INNER JOIN tblTopic ON tblForum.Forum_ID = tblTopic.Forum_ID) INNER JOIN (tblAuthor INNER JOIN tblThread ON tblAuthor.Author_ID = tblThread.Author_ID) ON tblTopic.Topic_ID = tblThread.Topic_ID"
 strSQL = strSQL & " WHERE (((tblForum.Password) Is Null))"
 strSQL = strSQL & " GROUP BY tblTopic.Topic_ID, tblTopic.Subject, tblForum.Forum_ID, tblTopic.Last_entry_date, tblForum.[Read]"
 strSQL = strSQL & " ORDER BY tblTopic.Last_entry_date DESC;"

I've added tblThread.[Thread_ID] in ''select top 12'', but I need to add something in FROM as well. I've never worked with inner join, so I don't know where to start.

Any help would be appreciated... The adjusted mod will be posted in the mods section once it's working.

Replies:

Posted By: fernan82
Date Posted: 08 December 2003 at 11:34pm

tblThread is already on the join so you don't need to add it again, I think (haven't tried it) that what you need to do is use the Last() function on tblThread.Thread_ID so it slects the thread ID for the last Thread_ID something like this: (I changed the format to be able to read it on a text editor)

strSQL   = "SELECT TOP 12 tblTopic.Topic_ID, tblTopic.Subject, tblForum.Forum_ID, " & _
       "Last(tblAuthor.Username) AS LastOfUsername, tblForum.[Read], Last(tblThread.Thread_ID) " & _
       "As LastPost FROM (tblForum INNER JOIN tblTopic ON tblForum.Forum_ID = tblTopic.Forum_ID) " & _
       "INNER JOIN (tblAuthor INNER JOIN tblThread ON tblAuthor.Author_ID = tblThread.Author_ID) " & _
       "ON tblTopic.Topic_ID = tblThread.Topic_ID " & _
       "WHERE (((tblForum.Password) Is Null)) " & _
       "GROUP BY tblTopic.Topic_ID, tblTopic.Subject, tblForum.Forum_ID, tblTopic.Last_entry_date, " & _
       "tblForum.[Read] ORDER BY tblTopic.Last_entry_date DESC;"

-------------
FeRnAN
http://www.danasoft.com/">

Posted By: pmormr
Date Posted: 09 December 2003 at 1:49pm

damn... looking at that sql statement is giving me a headache

-------------
Paul A Morgan

http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/

Posted By: psycotik
Date Posted: 12 December 2003 at 10:38am

2 main joins are inner join and left join. What they do is join tables acrosswise.

Eg.

Table1: CustomerID, FirstName, LastName
Table2: CustomerID, PhoneNumber

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2 on T2.CustomerID = T1.CustomerID

Will produce an output of:
CustomerID, FirstName, LastName, CustomerID, PhoneNumber

The main difference between the joins is that inner join eleminates non-matching records... so in the above example, if you used inner join and there wasnt a phone number record for each customer, they wont be displayed in the resulting query.

If you use left join and there is no matching phone number all T1 records will be displayed but with Null's in the fields for the second half of the query (because there isnt a match)

Posted By: Badaboem
Date Posted: 15 December 2003 at 5:15pm

Thanks for the help people. It made things a bit easier to understand. Now to see if I can get the enhanced mod working