Best method of pulling data.

[onlyican]

I am re-building one of my old sites, adding a few extra features.

 

Basically I have a list of stories and comments

I want to pull latest stories and the comments for them stories if any and where valid

 

Table Strus

tblStories

stories_id INT(11)

stories_owner INT(11)

stories_status TINYINT(2)

story_title VARCHAR(255)

 

tblUsers

users_id INT(11)

username VARCHAR(255)

user_level INT(11)

 

tblComments

comments_id INT(11)

comments_story_id INT(11)

comment_owner_id INT(11)

comment_text VARCHAR(255)

comment_status TINYINT(2)

 

** This is a basic stru and not correct

 

SO I have the basic query

** Using ALL as cant be assed to write every field here, but yes I would in real sitution

 

SELECT * FROM tblStories

INNER JOIN tblUsers ON tblStories.stories_owner = tblUsers.users_id

LEFT JOIN tblComments ON tblStories.stories_id = tblComments.comments_story_id

INNER JOIN tblUsers AS CommentUser ON CommentUser.users_id = tblComments.comment_owner_id

 

THIS works fine

 

But I have flags in each table, the *_status says if its pending / online ect (2 is online)

So a story / comment can be hidden / deleted

 

 

If I add the following

 

WHERE tblStories.stories_status = 2

AND tblUsers.users_level >=5

AND tblComments.comments_status = 2

AND CommentUser.users_level >= 5

 

Any story without comments does not show.

 

So basically I want to pull all stories that are live, (status = 2)

With all users who are valid users (user_level >= 5)

 

And all comments on the same. (status = 2 and user_level for the person who added comment >= 5)

 

I am doing the user, so if a user is removed, it will automatically remove all comments.

 

As mentioned, adding the WHERES does not bring up stories where there are no comments, as the comment_status is NOT greater than 2

 

[Buddski]

I'm not exactly sure where the question in there was?

[mikosiko]

try this (no tested):

 

SELECT * FROM tblStories
   INNER JOIN tblUsers ON tblStories.stories_owner = tblUsers.users_id AND tblUsers.users_level >= 5
   LEFT JOIN tblsComments ON tblUsers.users_id = tblComments.comment_owner_id  AND tblComments.comments_status = 2 AND  tblStories.stories_id = tblComments.comments_story_id
WHERE tblStories.stories_status = 2

 

[awjudd]

For the comments table join since it is a LEFT JOIN, if there are no matching results then it will return NULL.  Because of that you should be adding your condition for the status on that table = in the ON clause instead of in there WHERE clause.  Because if it appears in the WHERE clause it needs to find a comment to show up (you could just have the condition as: tblComments.comments_status = 2 OR tblComments.comments_status IS NULL).

 

~juddster