Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - inner join
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

inner join
 Post Reply Post Reply
Author
  Topic Search Topic Search  Topic Options Topic Options
Badaboem View Drop Down
Senior Member
Senior Member


Joined: 12 April 2002
Location: Netherlands
Status: Offline
Points: 600 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Badaboem Quote  Post ReplyReply Direct Link To This Post Topic: inner join
    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.



Edited by Badaboem
Back to Top
fernan82 View Drop Down
Mod Builder Group
Mod Builder Group
Avatar

Joined: 17 November 2002
Location: United States
Status: Offline
Points: 362 		Post Options Post Options   Thanks (0) Thanks(0)   Quote fernan82 Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
pmormr View Drop Down
Senior Member
Senior Member


Joined: 06 January 2003
Location: United States
Status: Offline
Points: 1479 		Post Options Post Options   Thanks (0) Thanks(0)   Quote pmormr Quote  Post ReplyReply Direct Link To This Post 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/
Back to Top
psycotik View Drop Down
Groupie
Groupie


Joined: 27 November 2003
Status: Offline
Points: 73 		Post Options Post Options   Thanks (0) Thanks(0)   Quote psycotik Quote  Post ReplyReply Direct Link To This Post 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)
Back to Top
Badaboem View Drop Down
Senior Member
Senior Member


Joined: 12 April 2002
Location: Netherlands
Status: Offline
Points: 600 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Badaboem Quote  Post ReplyReply Direct Link To This Post 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 .
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.