Professional Web Applications Themes

Joining Tables - MySQL

Hi I'm a bit stuck for a way to join 4 tables in a single query in MySQL. The tables relate to a submission system for users to submit 1 or more links against a specific topic. The tables are follows: // table 'topic' topicID topicName // table 'submission' submissionID topicID userID // table 'link' linkID linkURL submissionID // table 'user' userID userName I'm trying to get it so when I query it with a specific topic, I want the details of the topic, submissions and all associated links and user details. As theres no common table that joins them ...

  1. #1

    Default Joining Tables

    Hi

    I'm a bit stuck for a way to join 4 tables in a single query in MySQL.

    The tables relate to a submission system for users to submit 1 or more
    links against a specific topic. The tables are follows:

    // table 'topic'
    topicID
    topicName

    // table 'submission'
    submissionID
    topicID
    userID

    // table 'link'
    linkID
    linkURL
    submissionID

    // table 'user'
    userID
    userName

    I'm trying to get it so when I query it with a specific topic, I want
    the details of the topic, submissions and all associated links and
    user details. As theres no common table that joins them all, I'm not
    sure which table to start the query with and how to get a join to the
    other 3.

    Any help would be much appreciated.

    mou Guest

  2. #2

    Default Re: Joining Tables

    mou wrote: 

    SELECT tablename.column1, tablename.column2, tablename.column3,
    tablename.column4 FROM table1
    INNER JOIN table2 ON (table1.joincolumn=table2.joincolumn)
    INNER JOIN table3 ON (table2.joincolumn=table3.joincolumn)
    INNER JOIN table4 ON (table2.joincolumn=table4.joincolumn)
    WHERE tablename.topic='what you are looking for';

    Just change the column names and table names and you would have a go, just
    replace the "tablename.columnX, ..." to a * if you want all columns.

    --

    //Aho
    J.O. Guest

  3. #3

    Default Re: Joining Tables

    On Jun 3, 3:42 pm, "J.O. Aho" <net> wrote: 







    >
    > SELECT tablename.column1, tablename.column2, tablename.column3,
    > tablename.column4 FROM table1
    > INNER JOIN table2 ON (table1.joincolumn=table2.joincolumn)
    > INNER JOIN table3 ON (table2.joincolumn=table3.joincolumn)
    > INNER JOIN table4 ON (table2.joincolumn=table4.joincolumn)
    > WHERE tablename.topic='what you are looking for';
    >
    > Just change the column names and table names and you would have a go, just
    > replace the "tablename.columnX, ..." to a * if you want all columns.
    >
    > --
    >
    > //Aho- Hide quoted text -
    >
    > - Show quoted text -[/ref]


    Cheers fella, much appreciated.

    mou Guest

Similar Threads

  1. Joining three tables
    By DettCom in forum Coldfusion Database Access
    Replies: 4
    Last Post: July 19th, 06:12 PM
  2. joining 3 tables?
    By siti_nana in forum Dreamweaver AppDev
    Replies: 2
    Last Post: February 27th, 08:16 AM
  3. joining several tables
    By james in forum IBM DB2
    Replies: 3
    Last Post: October 17th, 01:42 PM
  4. HELP -- joining tables
    By JVince in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 30th, 08:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139