Since this deleted thread was revived, I would like to get my original problem fixed…
The query in the Original Post is NOT working as expected.
Because this is an SQL and database issue - to which all of you don’t have access to the data - it is harder to trouble-shoot, yet not impossible.
I did an echo of my PHP query to see what MySQL was receiving…
SELECT m.first_name, c.created_on, c.body, c.status
FROM member AS m
INNER JOIN COMMENT AS c ON m.id = c.member_id
WHERE c.article_id =2
When I run that output SQL string in phpMyAdmin, I get…
first_name created_on body status
Debbie 2012-01-08 14:09:27 Jan 8 2:09pm Pending
…however, if I look in my Comments table, there are 5 records which have an article_id=2
I think the problem is with my INNER JOIN because I delete Members regularly as I am testing my account creating and activation code.
So out of the 5 records in the Comments table where article_id=2…
3 records have a member_id=24,
1 record has a member_id=34,
1 record has a member_id=42
And as of today, member_id=42 is the only record existing in the ones mentioned above.
And as I am typing, that Member was created today, and the only Comment that I am getting for article_id=2 is dated TODAY just like member_id=42 who was created today.
Can someone help me figure this out?!
1.) Does it make sense to want to show Comments for which there is a deleted Member? (I know in production you shouldn’t delete records?!)
2.) Is there a way to show all Comments even for some of the Members that were deleted?
3.) If I used an Outer Join, could I show Comments whether or not they have a Member?