Mysql Join Issue

Question

Hi i need help with a query.

I have 4 tables

articles - id, category_id, user_id
categories - id
article_category - article_id, category_id
users - id

The category_id in the article table is used determine the main category

Now I want to get all articles so i uses the query below.

Article can have more than one category. I want to see a list of articles with the main category information attached

SELECT a.*, c.title , c.slug FROM articles a, article_category ac, categories c, users u
WHERE ac.article_id = a.category_id is supposed to be ac.article_id = a.id
AND ac.category_id = c.id
AND a.user_id = u.id
AND ac.category_id = '1'

Problem with this query is that i get the incorrect category information and i dont think i should run a subquery like the one below to solve it

(select title from categories where id = a.category_id) as title

Thanks

Answer 1

Your join clauses were incorrect. I have re-written it in ANSI syntax to make it more readable, and the JOIN clauses more explicit:

SELECT a.*, c.title , c.slug
FROM articles a
INNER JOIN article_category ac on ac.category_id = a.category_id
INNER JOIN categories c on ac.category_id = c.id
INNER JOIN users u on a.user_id = u.id
WHERE ac.category_id = '1'

Incidentally, you are JOINing against the users table but not selecting any columns from it, so you can remove that join unless you are using it to filter the results.

Answer 2

Removed users table from query (does not used at all), changed condition from ac.article_id = a.category_id to ac.category_id = a.category_id

      SELECT a.*, c.title , c.slug 
      FROM articles a, article_category ac, categories c
      WHERE ac.category_id = a.category_id
      AND ac.category_id = c.id
      AND ac.category_id = '1'

Answer 3

select a.*, c.title, c.slug
from articles a
join article_category ac on a.id = ac.article_id
join categories c on ac.category_id = c.id
join users u on a.user_id = u.id
where c.id = 1

Answer 4

you're joining on article_id=category_id, that doesn't make sense, try that instead:

SELECT a.*, c.title , c.slug FROM articles a, article_category ac, categories c, users u
WHERE ac.category_id = a.category_id
AND ac.category_id = c.id
AND a.user_id = u.id
AND ac.category_id = '1'