CODEWITHSUNDEEP
mysqlsql
BenM
BenM

Reputation: 53246

MySQL Joined SELECT COUNT()

I wish to select results across several tables, but I only want to return rows based on the COUNT() result of joined SELECT query.

Here's how the query looks at the moment:

SELECT `s`.`venue_id` AS `id`,
       CONCAT(`u`.`First_name`, ' ', `u`.`Surname`) AS `user_name`,
       `u`.`avatar` AS `avatar`,
       `u`.`facebookId` AS `fid`,
       `x`.`imgs` AS `num_imgs`
       FROM `new_shortlists_venues` `s`
       INNER JOIN `new_shortlists` ON `new_shortlists`.`id` = `s`.`list_id`
       INNER JOIN `users` `u` ON `u`.`id` = `new_shortlists`.`bride_id`
       LEFT JOIN (SELECT `listing_id`, COUNT(*) `imgs` FROM `listingsImages`) `x` ON `s`.`venue_id` = `x`.`listing_id`
       WHERE `new_shortlists`.`venues` > 4
       AND `new_shortlists`.`bride_id` != 0
       GROUP BY `s`.`list_id`
       ORDER BY `s`.`date_added` DESC
       LIMIT 6

For some reason, the query returns NULL for num_imgs. Essentially, I'd like to select only records which have at least 4 records in the listingsImages table.

Please note that this is for a legacy system, and I didn't design the DB! As a result, I have now option to change the schema.

Upvotes: 2

Views: 114

Answers (2)

sgeddes
sgeddes

Reputation: 62861

You left off the GROUP BY of your subquery. Your current query is returning COUNT(*) associated with a random listing_id. Add GROUP BY listing_id and you should return the correct counts.

SELECT `s`.`venue_id` AS `id`,
   CONCAT(`u`.`First_name`, ' ', `u`.`Surname`) AS `user_name`,
   `u`.`avatar` AS `avatar`,
   `u`.`facebookId` AS `fid`,
   `x`.`imgs` AS `num_imgs`
   FROM `new_shortlists_venues` `s`
       INNER JOIN `new_shortlists` ON `new_shortlists`.`id` = `s`.`list_id`
       INNER JOIN `users` `u` ON `u`.`id` = `new_shortlists`.`bride_id`
       LEFT JOIN (SELECT `listing_id`, COUNT(*) `imgs` 
                  FROM `listingsImages` 
                  GROUP BY `listing_id`) `x` ON `s`.`venue_id` = `x`.`listing_id`
   WHERE `new_shortlists`.`venues` > 4
       AND `new_shortlists`.`bride_id` != 0
   GROUP BY `s`.`list_id`
   ORDER BY `s`.`date_added` DESC
   LIMIT 6

And to return those with at least 4 records, just add that constraint to your WHERE criteria:

AND `x`.`imgs` >= 4

Upvotes: 2

Andomar
Andomar

Reputation: 238296

This might be the culprit:

ON `s`.`venue_id` = `x`.`listing_id`

Upvotes: 0

Related Questions