need help with Mysql queries.

[stopblackholes]

ok i have three tables

 

post table

-----------------

post_id --- category_id --- author_id -- content --- ect

 

 

categories table

-----------------

category_id --- category_name

 

 

users table

------

author_id --- username --- pw

 

 

whats the best way to query posts and fetch the related category_name and username with the post? how would i do it?

 

should i do three separate select queries? or is there a better way? performance wise

 

 

 

or say i have three table relationship. how would i query posts and show the related category with the post?

 

post table

----------------

post_id--- category_id ---content

 

 

category_post_rel table

------------------------

post_id  category_id

 

 

categories table

--------------------

category_id  category_name

 

 

I just started getting into mysql about a month ago i understand simple queries selects and joins. i know its better to normalize your database with many to many relationships but i just don't understand the complex queries. Id rather learn the right way now then go over the whole thing again in a few months.

 

[fenway]

Simply join the users and categories table to the posts table.

[stopblackholes]

ok thanks i think i got one query working (only pulled one result so far)

 

 

 

does this look correct?

 

$query = "SELECT * FROM post LEFT JOIN categories ON
categories.category_id=post.category_id LEFT JOIN users ON users.author_id=post.author_id WHERE post.post_id='$post_id'";

 

 

i was curious what type of join works best. INNER JOIN vs LEFT JOIN, or is there another more efficient way?

[fenway]

Depends what you're trying to do... INNER JOIN is better unless you want to retrieve posts that don't have matching categories/users.

[stopblackholes]

I guess inner join dont work for that query it returns nothing.  Also with left join  i cant get it to return the id for the category it returns 0.

 

 

[stopblackholes]

oops figured it out inner joins need

 

$query = "SELECT * FROM ((`post`
inner JOIN categories.category_id=post.category_id)
inner JOIN users ON users.author_id=post.author_id)"; 

[stopblackholes]

i guess using multiple inner joins dont work with where clause? that seems to be the problem for me.

 

one inner join works ok with where clause.

[fenway]

You don't need the parens.

[gluck]

oops figured it out inner joins need

 

$query = "SELECT * FROM ((`post`
inner JOIN categories.category_id=post.category_id)
inner JOIN users ON users.author_id=post.author_id)"; 

 

 

you are missing the second table in you join - categories