Justin French wrote:

Can anyone help me optimise the way I do this?

I have two tables, 'article' and 'user'. Article has a user_id which related to the user table.

When selecting all articles from the the article table, I'd like to be able to get the username of the user_id. Currently I'm doing this as a separate query whilst looping through the articles, but this is obviously causing way too many DB calls.

Yeah, you don't want to do that.

some pseudo code:

SELECT * FROM article
foreach article
    {
    SELECT username FROM user WHERE id=$user_id
    }

Perhaps I need a join, or maybe just a more complex query -- can any one lend a hand?

Yes, you need a join.

My guess is maybe something like

SELECT article.title, article.user_id, user.username
FROM article, user
WHERE user.id = article.user_id

Good guess. Have you tried it? That should do exactly what you want. You could make it slightly shorter to type by using table aliases, like this:


  SELECT a.title, a.user_id, u.username
  FROM article a, user u
  WHERE u.id = a.user_id;

See the manual for details on SELECTs <http://dev.mysql.com/doc/mysql/en/SELECT.html> and JOINS <http://dev.mysql.com/doc/mysql/en/JOIN.html>.

---
Justin French
http://indent.com.au

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]



Reply via email to