On Jan 30, 2007, at 4:33 PM, Skip Evans wrote:
Hey all,
I have the following query:
$sql="SELECT count(*) AS
count,votes.storyID,stories.title,stories.storyID as
sID,stories.approved, stories.story,stories.userID, fname, lname
FROM `bsp_story_votes` as votes, bsp_story_stories
AS stories, users AS usr
WHERE votes.storyID=stories.storyID AND
stories.userID=usr.id AND stories.contestID=$contestID
GROUP BY votes.storyID
ORDER BY stories.approved,count DESC, sID ASC LIMIT
$b_recno,$recs";
How would this need to be changed so that it would return rows for
the members of the bsp_story_stories table that do not have records
in the bsp_story_votes table?
Is that what the left/right joins do???
Thanks!
Skip
This may be closer to what you're wanting...
<query>
SELECT count(stories.*) AS count, votes.storyID, stories.title,
stories.storyID AS sID, stories.approved, stories.story,
stories.userID, fname, lname
FROM bsp_story_votes AS votes LEFT JOIN (bsp_story_stories AS stories
ON votes.storyID = stories.storyID) INNER JOIN users AS usr ON
stories.userID = usr.id
WHERE stories.contestID=$contestID
GROUP BY votes.storyID
ORDER BY stories.approved, count DESC, sID ASC LIMIT $b_recno, $recs
</query>
If that doesn't work, you may try changing the LEFT to RIGHT. I
probably screwed it up, but that's closer to how JOINs work. I'm not
sure if the GROUP BY will work with this query either??? I thought
you had to use HAVING with a GROUP BY? Refer to the M*SQL manual.
Now that think about it... I don't know if you can have a 'count()'
in with that query. If it doesn't work, you may try pulling count()
out and just using m*sql_num_rows() afterwards to get the count. Hope
that helps!
~Philip
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
