Back to discussion

SQL Join Statement

Last post was 27 Oct 2003 at 15:20
sql Brisbane, Australia
  • 17 years ago

    Hello


    I don't know to much about the advanced SQL statements. I am trying to make a Join statement to improve performance.


    At the moment i have the following Statements:
    In VB/ASP Code:

    Code:
    Set objRSNews = objConn.Execute("SELECT * FROM forumtopics WHERE boardid=1 ORDER BY topicid DESC")
    Set objRSNewsMember = objConn.Execute("SELECT * FROM members WHERE memberid=" & objRSNews("postedby"))
    In SQL:
    Code:
    SELECT * FROM forumtopics WHERE boardid=1 ORDER BY topicid DESC
    SELECT * FROM members WHERE memberid=POSTEDBY


    I want to put these two statements into one; this might be able to be achivied with a simple reference between two tables.


    More information:
    forum_topic:
    - topicid (Primary Key)
    members:
    - memberid (Primary Key)

    Report abuse
  • 17 years ago

    I assume postedby by is the field that holds the memberid in the forum_topics table....


    Code:

    SELECT * FROM forumtopics
    INNER JOIN members ON forum    topics.postedby = members.memberid
    WHERE forumtopics.boardid = 1
    ORDER BY forum    topics.topicid DESC


    this syntax may change - what SQL engine are you using?

    Report abuse
  • 17 years ago

    Hello


    Thanks, it works like a charm, that is what i was looking for


    I am using Access to design my db but MySQL, Access and MSSQL will be used, maybe even others. Will i have to have seperate queries for each db type?


    Thanks for your time

    Report abuse
  • 17 years ago

    Glad to help


    Depending on what you want to do you can use a left or right join instead of the inner type. It is best to stick to the left or inner join as they are defined to be semantically equivalent whereas the right join can restrict code portability across databases.


    If you want to learn more, w3schools has a good tutorial.


    Report abuse
  • 17 years ago

    jonorossi, you are on the right track. JOINs will definitely improve on your application performance. There are many types of joins and i think you will need to find more resource on that in the internet. I'm nobody to explain exactly what are the functionalites that they are good in. But what i can do is just merely guide you tru on how to do this. The 2 basic JOINs that you can learn first is INNER JOIN and OUTER JOIN. OUTER JOIN is then separated into another 2 more which is LEFT OUTER JOIN and RIGHT OUTER JOIN. According to some sources, OUTER JOIN is much faster then INNER JOINs. But of cos, that depends on what situation you're in.


    As for your case, i think i would need to know which two fields are linked in the forum table and the members table...

    Report abuse
  • 17 years ago

    Hello


    Sorry i haven't replied lately, i have been a bit busy, it is finally the weekend and not more work until monday.


    I have been to w3schools before, actually when i first started with ClassicASP, i have all the pages saved on my computer for reference and it didn't help to much but i see that it has been updated which usually doesn't happen to many websites. Joins are explained heaps better now thanks to you guys and to w3schools updating.


    Thanks again, I will read into Joins and work out the best and effective way to use them.

    Report abuse
  • 17 years ago

    Hello


    I have a few more questions. Is there any performance difference between the following statements? The Join and a table reference.

    Code:
    SELECT * FROM forumtopics
    INNER JOIN members ON forum    topics.postedby = members.memberid
    WHERE forumtopics.boardid = 1
    ORDER BY forum    topics.topicid DESC


    Code:
    SELECT * FROM forumtopics, members
    WHERE forum    topics.postedby=members.memberid
    AND forumtopics.boardid=1
    ORDER BY forum    topics.topicid DESC


    Also is the a way to map/join three tables together? I tried doing this:

    Code:
    SELECT * FROM forumtopics, members, forumposts
    WHERE forumtopics.postedby=members.memberid
    AND forum    topics.postedby=forumposts.postedby
    AND forum    topics.boardid = 1
    ORDER BY forum_topics.topicid DESC


    Table Name      Primary Key     Reference/Join Column
    forumtopics     topicid              topicid
    forum    posts      postid              topicid
    members          memberid        memberid


    If you cannot understand what i am trying to ask for then i will explain it in more detail
    Thanks for all your help

    Report abuse
  • 17 years ago

    regarding your second question, yes you can Join as many tables as you want...

    Report abuse
  • 17 years ago

    Hello


    Thanks for the answer. Do you join three tables like this?

    Code:
    SELECT * FROM forumtopics
    INNER JOIN forum    posts ON forumtopics.postedby = forumposts.topicid
    INNER JOIN members ON forumtopics.postedby = members.memberid
    WHERE forum    topics.boardid = 1
    ORDER BY forum_topics.topicid DESC


    I am not on my computer so i don't have my stuff to try it. I will be able to  try it in a few minutes after my computer has finished burning a disc.


    Thanks again

    Report abuse
  • 17 years ago

    yes man, you got it right. That is what you should do. You see fren, you should know what is INNER JOIN AND OUTER JOIN, it will improve your performance drastically. Use whichever when necessary. From the query of yours, you are selecting all the fields from the forum_topics.


    Just a tip, but i think you already know this, if you want to select some fields which are not from the forum_topics table, then you can try doing this:


    Code:

    SELECT *, fp.field, m.field FROM forumtopics ft
    INNER JOIN forum    posts fp ON ft.postedby = fp.topicid
    INNER JOIN members m ON ft.postedby = m.memberid
    WHERE ft.boardid = 1
    ORDER BY ft.topicid DESC


    Hope this helps you...

    Report abuse
  • 17 years ago

    a brief but inciteful article, click here

    Report abuse

Post a reply

Enter your message below

Sign in or Join us (it's free).

SQL forum discussion

SQL tutorials

Quick links

Recent activity

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Web Development

Developer Jobs

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

Cancel
“Measuring programming progress by lines of code is like measuring aircraft building progress by weight.” - Bill Gates