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]
