small problem in mysql query?

i have small problem in my sql query

my tables

 /*  threads
    thread_id/thread_title/thread_content
    1 / any post title / welcome to my post
    relations
    cate_id/thread_id
    1 / 1
    2 / 1
    categories
    category_id/category_name
    1 / some_cate
    2 / second_cate
    */

My sql query

$q = mysql_query("SELECT t.*,c.*, GROUP_CONCAT(r.cate_id SEPARATOR ' ') as cate_id 
FROM threads as t
LEFT JOIN relations as r on r.thread_id = t.thread_id
LEFT JOIN categories as c on c.category_id = r.cate_id
GROUP BY r.thread_id
");

php code

while($thread = mysql_fetch_array($q)){
    echo 'Post title is: ' . $thread['thread_title'] . '<br />'; // work fine
    echo 'Post content is: ' . $thread['thread_content'] . '<br />'; //work fine
    echo 'Categories id is : ' . $thread['cate_id'] . '/' . '<br /&g开发者_如何转开发t;'; //  cate_id of relations table work fine
    echo 'Categories names is : ' . $thread['category_name'] . '/'; // category name of categories table don't work fine
    echo '-------End of first POOOOOOOOOOOST--------';
}

OUTPUT

/*
any post title
welcome to my post
1/2
some_cate/
-------End of first POOOOOOOOOOOST-------
*/

Now my problem is!

There is small problem in query

there is two categories id (1 and 2)

should be there is two categories name!

some_cate / second_cate

but it display only one! though it display two categories id!

categories names does not repeat

but the categories id is repeat! and working fine

@@Doug Kress

i tryid your code but there is problem in your code with mysql_fetch_array

i got duplication of posts!

any post title
welcome to my post
some_cate/

any post title
welcome to my post
second_cate/

i am using CONCAT and GROUP BY to avoid this problem

---

The problem is actually in the GROuP BY - you're telling it to group by r.thread_id - based on your example, there's only one thread_id (1), so it will only return one record.

I'm guessing you don't need the GROUP BY or the GROUP_CONCAt at all.

SELECT t.thread_title, t.thread_content, r.cate, c.category_name
FROM threads as t
LEFT JOIN relations as r on r.thread_id = t.thread_id
LEFT JOIN categories as c on c.category_id = r.cate_id

It's usually best to specify all of the fields that you're going to use. Otherwise, it's unnecessary work for MySQL and for PHP, and it doesn't make your intent very clear.

I don't know the data, but based on your sample, you could change the LEFT join to an INNER join.

---

You must add GROUP_CONCAT(c.category_name, ' ') as category_name at your SELECT statement

If you will meet problem with same column name then just rename category_name to something else.