1

I need to select all users whether they have images or not If they have then select the primary image. It could be simple for you but looking difficult for me.

I have 2 tables users , images 

Users Table

Id | name | 
1    xyz    
2    abc
3    qwe
4    rty

Images Table

Id  | user_id | image_path  | is_primary
1     1           path          0
2     1          path           1
3     2          path           1
4     4          path           0



**Result I'm expecting**

name  |  image_path  | is_primary
xyz   |  path        | 1
abc   |  path        | 1
qwe   |  null        | 0         (this user has no image in images table)
rty   |  path        | 0

I have no clue how to do it. It looks complicated to me as I know it could not be done with only "AND" , "Having" or "sub query".

learner
  • 171
  • 2
  • 11

2 Answers2

0

The subquery on the right side the LEFT JOIN below restricts the Images table to only those records having the maximum is_primary value for each user. When the user has no primary, the max value for is_primary is zero, and when he does have a primary, the max value is 1.

SELECT t1.name,
       t2.image_path,
       COALESCE(t2.is_primary, 0) AS is_primary
FROM Users t1
LEFT JOIN
(
    SELECT t1.user_id,
           t1.image_path,
           t1.is_primary
    FROM Images t1
    INNER JOIN
    (
        SELECT user_id, MAX(is_primary) AS is_primary
        FROM Images
        GROUP BY user_id
    ) t2
    ON t1.user_id    = t2.user_id    AND
       t1.is_primary = t2.is_primary
) t2
    ON t1.Id = t2.user_id

Demo here:

SQLFiddle

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • can we remove repetition from this query. @Tim – learner Nov 25 '16 at 06:14
  • @learner Not without you providing logic to do this. Give us rules you want which would filter out the second row. – Tim Biegeleisen Nov 25 '16 at 06:15
  • each user can has only one primary image so if the user has primary image then get that image from database or put null if no primary image or no record found for that user in mage table. Hope so it will help to put some logic. @tim – learner Nov 25 '16 at 06:21
  • but max one image record should be against each user – learner Nov 25 '16 at 06:22
0

Try this:

SELECT 
    users.name, Images.image_path, IF(MAX(Images.is_primary)=1, 1, 0)
FROM 
    users 
LEFT JOIN 
    Images 
ON 
    Images.user_id = users.Id
GROUP BY 
    users.Id

Lets add An aggregate with MAX...

barudo
  • 665
  • 4
  • 13
  • it has repetition, is it possible to remove it, i need to select only 1 primary image not all if exist – learner Nov 25 '16 at 06:17