MySQL join selects

[dphiffer]

I'm trying to make a content management system for a news-oriented website and I'm running into some troubles with the SQL code to make it work just right.

Basically I have three tables: user, article, author. I separated authorship part into its own table to allow for multiple authors for a single article. Furthermore, each article has an 'owner' column which associates with the user's 'id' - this is used so that only one person can edit an article at a time.

I want to select articles from the database owned by the currently logged in user, in this case denoted by the PHP variable $user. After reading up on JOIN SELECTs in the MySQL docs I tried the following:

select * from article
left join author on article.id = author.article
where article.owner=$user

This works great until you add another author to the mix - this causes a duplicate selection to be made of the same article. Now I realize I could write some PHP code to iterate over search results, but I feel like there ought to be a way to accomplish the effect I want using SQL alone.

TIA for any help.

[Elena_Mitovska]

Maybe it will help if you add "distinct" in your select statement:
It is supposed to remove all duplicate records from result set.

Select distinct * from ......

Please, let me nkow if it helped.

[tomhath]

Why are you joining to the author table at all? All you are returning are the rows from article, based on the article.owner column. Remove the join and it should work fine.

Elena's suggestion will also work, but it will be slower because of the join.

[dphiffer]

Yeah, there's no need for that join in there. I didn't know about the DISTINCT keyword before, so I guess it's not all a waste of time. Thanks for the replies!