Help with Joins!!!

[kycan]

Why does this query return two results?? There is only one record in the news table!!!

mysql_query("SELECT a.name, a.email, n.title, n.submit_time, n.body FROM news n, administrators a, categories c WHERE n.category_id = c.category_id AND c.category_id = 1")

If you want to see what the two results are, go here: http://www.zikes.com

[keegan]

I think it's because there are two administrators. As far as I can tell, you don't link the name and email data to the actual article in any way - except to list them on the page. This seems to be a flaw in your reasoning. Do you have a way to tell which administrator should be credited for the article?

-Keegan

[kycan]

Yes, the "news" table has a admin_id field which picks up which person posted the news by a unique id. The id points to a persons name in the "administrators" table.

[keegan]

Okay, so the query you need is:

SELECT a.name, a.email, n.title, n.submit_time, n.body
FROM news AS n, administrators AS a, categories AS c
WHERE n.category_id = c.category_id
AND c.category_id = 1
AND n.admin_id = a.id

(I assumed that the id field in administrators is called id - you might need to change that).

-Keegan

[kycan]

Thanks, worked nicely. =)