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 wrote:
> 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.

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

On Jun 3, 3:42 pm, "J.O. Aho" <u...@example.net> wrote:
> mou wrote:
> > 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.
>
> 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 -


Cheers fella, much appreciated.