Re: Help wtih a query?

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