Answer the question
In order to leave comments, you need to log in
How to correctly use JOIN to select data from multiple tables + COUNT(*)?
Hello!
There is a table with records -`boycotts` with fields (id, title, description, public)
There is a table - `likes` with fields (boycottid, userid) - 2 boycott records with id = 1
There is a table - `dislikes` with fields ( boycottid, userid) - 3 boycott records with id = 1 There is a table -
`
subscribtions` with fields (boycottid, userid) - 6 boycott records with id=1
data on the number of likes, dislikes and subscribers.
2. I have already tried to construct a query and noticed that the column with likes, for example, will be called COUNT(`likes`.`boycottid`). And usually I just by the name of the column and got the data from the MYSQL result. Of course, I can write such a column name in the code, but it just looks unusual and makes me think "maybe I'm doing something wrong?".
This is the query I got:
SELECT
`boycotts`.`id`,
`boycotts`.`title`,
`boycotts`.`description`,
`boycotts`.`public`,
COUNT(`likes`.`boycottid`),
COUNT(`dislikes`.`boycottid`),
COUNT(`subscriptions`.`boycottid`)
FROM
`boycotts`
JOIN `likes` ON `likes`.`boycottid`=`boycotts`.`id`
JOIN `dislikes` ON `dislikes`.`boycottid`=`boycotts`.`id`
JOIN `subscriptions` ON `subscriptions`.`boycottid`=`boycotts`.`id`
WHERE `boycotts`.`id`=1
SELECT
`boycotts`.`id`,
`boycotts`.`title`,
`boycotts`.`description`,
`boycotts`.`public`,
COUNT(`likes`.`boycottid`)
FROM
`boycotts`
JOIN
`likes`
ON
`likes`.`boycottid`=`boycotts`.`id`
WHERE
`boycotts`.`id`=1
Answer the question
In order to leave comments, you need to log in
SELECT `b`.`id`, `b`.`title`, `b`.`description`, `b`.`public`,
(SELECT COUNT(*) FROM `likes` WHERE `boycottid` = :boycot_id) AS `likes`,
(SELECT COUNT(*) FROM `dislikes` WHERE `boycottid` = :boycot_id) AS `dislikes`,
(SELECT COUNT(*) FROM `subscriptions` WHERE `boycottid` = :boycot_id) AS `subscriptions`
FROM (
SELECT `id`, `title`, `description`, `public` FROM `boycotts` WHERE `id` = :boycot_id
) AS `b`
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question