I have a table where I keep comments on topics:
Id, id_tema, id_autor, comentario, fecha
I need to ask a question that returns the number of comments after a certain comment on the same topic. The data I have is the Id of the comment, I have tried this but it does not return results:
SELECT COUNT(id) AS num_comentarios
from sala_muro_comentarios
WHERE Id=36251
and Id > 36251
group by id_tema
How could I make that query ?. Thanks!
Let's see why you failed what you tried:
WHERE Id=36251
and Id > 36251
You are asking if each record has id = 36251 and if, in addition, at the same time, each record has id greater than 36251. Obviously, a record has a unique id, so it fulfills one of the two conditions, but not the two at the same time. and how you put it, it's not going to work, since it's both at the same time.
Now, assuming that we can use the id as a basis, that is, the greater the id, the comment is newer, your query should be something like this:
SELECT COUNT(id) AS num_comentarios
from sala_muro_comentarios
WHERE id_tema=(select id_tema from sala_muro_comentarios where Id = 36251)
and Id > 36251
group by id_tema
What does this query ???? The inside part:
select id_tema from sala_muro_comentarios where Id = 36251
Returns a single record, with the subject_id of that comment. So, that's our subject, and the one we use in the main query. And then, we check it only for comments later than this one.
Sure, but with the second query you can do it:
SELECT COUNT (*) AS total_registers FROM comment_table WHERE (subject_id = XX) AND (comment_id
What do you want to do exactly? A counter for example of comments on a topic? Depends on what you need there are several ways to do it but it would be nice to know the objective.
If you want to know how many comments there are, then make a COUNT (*) of records associated with that particular topic (subject ID) subtract 1 and you would already have it.
SELECT COUNT (*) AS logs_totals FROM comment_table WHERE (subject_id = XX);
This would return the total. (Where XX is the ID of the topic from which the comments hang)
If you want to know how many comments there are before a concrete one that we say is out there in the middle, it is simply with the ID of that comment to make a stop:
SELECT COUNT (*) AS total_registers FROM comment_table WHERE (subject_id = XX) AND (comment_id