L
L
LemanRass212016-10-26 15:54:07
MySQL
LemanRass21, 2016-10-26 15:54:07

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

But for some reason it gives out that for the first boycott there are 36 likes and dislikes and subscribers, although there are not even such a number of records in any of the tables.
Although if you remove all JOINs except for one:
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

then it works fine.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
Rsa97, 2016-10-26
@LemanRass21

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`

But this is only if needed for a specific id.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question