Mysql> How to join data of 2 tables with single Query? (Select * and COUNT)-CodePudding

My sample data Table 'posts': | id (string) | Author (int)| desc (string)| | --- | --- |---| | bac| 111| Hello| | bak| 113| world|

table 'rating_info' | post_id(string)| rating (int)| | --- | --- | | bak| 111 | | bak| 112 | | bak| 114 | | bac | 114| | bac | 114|

My end goal:

Somehow combine both tables, Select everything from Posts, but only select the COUNT from rating_info.

what i have tried:

SELECT (SELECT *
               AS posts
        FROM   posts
        WHERE  posts.id= 'bac')       AS posts,
       (SELECT Count(rating_info.post_id) AS count2
        FROM   rating_info
        WHERE  rating_info.post_id = 'bac') AS hits

error result: #1241 - Operand should contain 1 column(s)

CodePudding user response：

The error comes from trying to display many columns AS posts.

You should use join:

SELECT *, Count(rating_info.post_id) AS count2 FROM posts
INNER JOIN rating_info ON rating_info.post_id = posts.id
WHERE posts.id = 'bac';

This displays all columns from both tables the count, you can switch the * to specific columns instead.