JOIN 3 tables, only able to get 2 working.

[MasterACE14]

Good Day,

 

I'm trying to JOIN 3 tables with a query, I can get it to work fine with 2, but having no luck with 3. Firstly here is my table names and their Primary/Foreign Keys.

 

Table: categories

Primary Key: cat_id

 

Table: forums

Primary Key: forum_id

Foreign Key: cat_id

 

Table: threads

Primary Key: thread_id

Foreign Key: forum_id

 

My current query SELECT's fields from `categories` and `forums` JOIN'ing them with `cat_id` and this works fine:

SELECT `categories`.`cat_id`, `categories`.`cat_name`, `forums`.`forum_name`

                                        FROM `categories`

                                        INNER JOIN `forums`

                                        USING (`cat_id`)

                                        ORDER BY `categories`.`cat_order` ASC

But I also want to SELECT `threads`.`thread_id` which needs to be JOIN'ed by... `threads`.`forum_id` = `forums`.`forum_id` somehow, I've tried several different combinations and can't get it to work.

 

Thanks for any help!

 

Cheers,

Ace

 

[mikosiko]

I've tried several different combinations and can't get it to work.

 

post some of the combinations that you have tried and explain why they "don't work"

[MasterACE14]

SELECT `categories`.`cat_id`, `categories`.`cat_name`, `forums`.`forum_name`, `threads`.`thread_id`

                                        FROM `categories`, `forums`, `threads`

                                        WHERE `categories`.`cat_id` = `forums`.`cat_id`

                                        AND `forums`.`forum_id` = `threads`.`forum_id`

                                        ORDER BY `categories`.`cat_order` ASC

Returns zero results.

[Muddy_Funster]

have you tried:

SELECT `categories`.`cat_id`, `categories`.`cat_name`, `forums`.`forum_name`, `threads`.`thread_id`
FROM `categories`
INNER JOIN `forums`ON (categories.cat_id = forums.cat_id)
LEFT JOIN `threads` ON (threads.forum_id = forums.forum_id)

ORDER BY `categories`.`cat_order` ASC

[MasterACE14]

That's done the trick, thank you kindly Muddy.

 

Regards,

Ace