How do I get the number of matching variables in two tables in SQL?

Asked Aug 02 '21 at 21:42

Active Aug 02 '21 at 21:46

Viewed 16 times

I am trying to query a post, its count(replies) and count(likes) with the following SQL query:

SELECT *, 
(SELECT COUNT(*) FROM Post WHERE P.Id = ThreadId), 
(SELECT COUNT(*) FROM Like WHERE P.Id = Id)
FROM Post AS P 
WHERE ThreadId = 0;

Unfortunately, this syntax seems to be incorrect. Where is my error, I can't quite figure it out. This is the error text generated by MySQL:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Like WHERE P.Id = Id) FROM Post AS P WHERE ThreadId = 0 ORDER' at line 1

edited Aug 02 '21 at 21:46

asked Aug 02 '21 at 21:42

moccajoghurt

1

"*Unfortunately, this syntax seems to be incorrect*" How do you *know* your syntax is incorrect? Can you share what your expected behavior of this query is vs. what you're seeing? Can you provide some sample data for us to understand what you're working with? Can you edit your question to include all pertinent error messages (if any)? – esqew Aug 02 '21 at 21:44
I added the error text. – moccajoghurt Aug 02 '21 at 21:46
`Like` is a reserverd word. You need to put it in backticks to use it as a table name. – Barmar Aug 02 '21 at 21:47
Or better, choose a name that is not a reserved word. – Tangentially Perpendicular Aug 02 '21 at 21:48
Guys, I thank you a lot. You just made my day. – moccajoghurt Aug 02 '21 at 21:50

How do I get the number of matching variables in two tables in SQL?

0 Answers0