Advertisement
Guest User

09.Profile Pictures

a guest
Oct 20th, 2018
94
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 0.49 KB | None | 0 0
  1. SELECT
  2.     `u`.`id`,
  3.     `u`.`username`,
  4.     CONCAT(`p`.`size`, 'KB') AS `size`
  5. FROM `users` `u`
  6. INNER JOIN `pictures` `p`
  7.     ON `p`.`id` = `u`.`profile_picture_id`
  8. WHERE `profile_picture_id` IN(
  9.     SELECT `profile_picture_id` FROM (
  10.         SELECT
  11.             `profile_picture_id`,
  12.             COUNT(`profile_picture_id`) AS `pic_count`
  13.         FROM `users`
  14.         GROUP BY `profile_picture_id`
  15.         HAVING `pic_count` > 1 #if someone has NULL profile_pic_id then the COUNT is 0 and skipped
  16.     ) AS `cq`
  17. )
  18. ORDER BY `u`.`id` ASC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement