Page 1 of 1
help with a join query
Posted: Fri Feb 17, 2006 9:40 pm
by s.dot
I have this query
Code: Select all
$forum_post_result = mysql_query("
SELECT
count(*) AS `forumnum`,
`author`
FROM
`forumentries`
GROUP BY
`author`
ORDER BY
`forumnum`
DESC LIMIT 20
") or die(mysql_error());
Basically i need to check the `username` in the `users` table to make sure that username still exists. I believe I need a join here, eh?
Posted: Fri Feb 17, 2006 9:48 pm
by josh
Code: Select all
SELECT
count(`forumentries`.*) AS `forumnum`,
`forumentries`.`author`,
`users`.`username` as `exists`
FROM
`forumentries`
LEFT JOIN `users` on `users`.`username` = `forumentries`.`author`
GROUP BY
`forumentries`.`author`
ORDER BY
`forumentries`.`forumnum`
DESC LIMIT 20
Something like this? `exists` will be empty (or NULL) if the user does not exist. By the way this thread should be moved to databases. You also might experience better performance running the query inside of your loop (when you're looping over the resultset), it all depends on your exact table setup.
Posted: Fri Feb 17, 2006 9:56 pm
by s.dot
really?
i thought queries inside of loops were a big no-no
plus i can't run the query inside the loop anyways, because i want to limit it to the top 20 results, if one of the users doesn't exist, then it will return 19
Posted: Fri Feb 17, 2006 10:10 pm
by josh
It depends, on some databases it could perform a full-table join for each record, example
user1 a
user1 b
user2 a
user2 b
and then go down and refine it, in the case mysql is doing this the loop would be faster. Don't be afraid to use a lot of queries per page view, its the expense of the queries themselves that matters.
Posted: Sat Feb 18, 2006 6:39 am
by duk
try to see using DISTINCT very very much faster then JOIN or IN
check my last threat in databses forum about distinct
viewtopic.php?t=44117