Adding 2 ANDs to WHERE clause

Databases
1

Im trying to count the # of replies a post gets using this

SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, members.name, 
COUNT(rply.parent_id) AS replies
FROM messages AS mess
INNER JOIN members 
ON members.id =  mess.user_id
LEFT OUTER JOIN messages AS rply
ON rply.parent_id = mess.mess_id
WHERE mess.topic_id = :id
AND mess.parent_id IS NULL
GROUP BY mess.mess_id

the replies column works good and produces the # of messages. Since I like to screw things up, I added a column, display to the messages table which is either 0 or 1
since I only want to count messages where display = 1, how do I incorporate that so that replies only counts the messages who have a display of 1?

Thanks…

2

Basically add your conditions to WHERE clause with an AND:

SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, members.name, 
COUNT(rply.parent_id) AS replies
FROM messages AS mess
INNER JOIN members 
ON members.id =  mess.user_id
LEFT OUTER JOIN messages AS rply
ON rply.parent_id = mess.mess_id
WHERE mess.topic_id = :id
AND mess.parent_id IS NULL
AND mess.display = 1
AND rply.display = 1
GROUP BY mess.mess_id

This would filter out all invisible messages and invisible replies.

3

no, sorry

you must AND this condition to the ON clause

reason: LEFT OUTER JOIN still wants to count 0 when appropriate

adding it to the WHERE clause effectively makes it an inner join and 0 counts will not show up

4

ok, put the ANBD thing after the ON clause, ran the query in PHPMyAdmin,

1920×1080 105 KB

But the replies is showing 2, shouldn’t it be 1 (the display is 0 on one of the messages in the messages table.
1920×1080 132 KB

(I tried adding AND prly.display = 1 but got an error)

5

i cannot read that phpmyadmin diagram

could you please post your query as text

6

Sorry, here it is

SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, users.name, mess.display,
COUNT(rply.parent_id) AS replies
FROM messages AS mess
INNER JOIN users
ON users.id = mess.user_id
AND mess.display = 1
LEFT OUTER JOIN messages AS rply
ON rply.parent_id = mess.mess_id
WHERE mess.topic_id = 1
AND mess.parent_id IS NULL
GROUP BY mess.mess_id

I really dont see the need for a few of the elements (do I need to SELECT mess.display and since I dont want to count to entry where the parent_id is NULL, shouldn’t that part be AND mess.parent_id IS NOT NULL)

Thanks

7

guess that bit was important (I made it bigger)

1920×1080 331 KB

8

i took your query and slightly rewrote it (i moved mess.display = 1 to the WHERE clause)

SELECT mess.subject , mess.message_txt , mess.mess_id , mess.created , users.name , COUNT(rply.parent_id) AS replies FROM messages AS mess INNER JOIN users ON users.id = mess.user_id LEFT OUTER JOIN messages AS rply ON rply.parent_id = mess.mess_id WHERE mess.topic_id = 1 AND mess.display = 1 AND mess.parent_id IS NULL GROUP BY mess.mess_id
does this query do what you want?

9

No, IPut the query in phpmyadmin

1920×1080 279 KB

the replies shows 2, but 1 of the messages reply (I turned its display to 0), why is that message counted as a reply if I turned its display off?

1920×1080 327 KB

There are 2 replies to the message (#3, where the parent_id is NULL) but since I turned 1 of those messages “off” shouldnt the count be 1?

10

i am sure that if you match your actual results back to the sql logic in the query, you will see where a modification must be made in order to get what you want

11

Because nowhere in your query did you filter out replies that have display set to 0. Which means you have to add rply.display = 1 to your join condition.

12

k, that was it (thanks for the hint freakyrag), I forgot that a rply table was created and had check each records display property to make sure its set to 1.

Thanks.

13

heres the result (the counter link on the right reads 1)
http://coronadoshores.ca/community/category.php?id=1&topic=Welcome
Now im toying with the query (on the page you are taken too after clicking replies).
This is the current query

SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, users.name
FROM messages AS mess
INNER JOIN `users` 
ON users.id =  mess.user_id
WHERE mess.parent_id = 3
OR mess.mess_id = 3
ORDER BY mess.created

So, now that I got to only include where display is 1, should I change it to

SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, mess.display, users.name
FROM messages AS mess
INNER JOIN `users` 
ON users.id =  mess.user_id
WHERE (mess.parent_id = 3
OR mess.mess_id = 3)
AND mess.display = 1
ORDER BY mess.created

ok?

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.