Unable to fetch correct image path from table

Question

I am trying to fetch users id,fname,lname,image from Profile album with cover value Yes.

I am using following query

select distinct
        a.id as id,
        a.fname as fname,
        a.lname as lname,
       im.sm_pic_path as image,
       im.is_album_cover as cover,
        album.name as album
    from
        user a 
    left outer join
        images im 
            on a.id=im.aid 
    left outer join
        album album 
            on im.album_id=album.id 
    where

            a.fname like '%man%'
            or a.lname like '%man%'

        and a.status='active' 
        and im.is_album_cover='Yes' 
        and album.name='Profile'
        group by a.id
    order by
        im.sm_pic_path desc,a.id 
          limit 0,30

This query is working without error but it is not fetching correct image. I want image whose album name is profile and is_album_cover value is yes.

How to make this correct.

Answer 1

For me there are several errors in your query:

• In your query you have done an INNER JOIN between your user and images table because you have put in the WHERE clause this condition: and im.is_album_cover='Yes'
• In your WHERE contidion you must use brackets for OR
• GROUP BY clause must be extended for all fields without an aggregate function in the select list field otherwise MySql engine take the first row.

So your query, for me, can be re-write in this way:

SELECT
    a.id as id,
    a.fname as fname,
    a.lname as lname,
    (SELECT im.sm_pic_path 
    FROM images im
    WHERE a.id=im.aid
    AND im.is_album_cover='Yes') as image,
    (SELECT im.is_album_cover
    FROM images im
    WHERE a.id=im.aid
    AND im.is_album_cover='Yes') AS cover,
    (SELECT album.name
    FROM images im
    JOIN album 
        ON im.album_id=album.id
    WHERE a.id = im.aid
    AND album.name='Profile'
    AND im.is_album_cover='Yes') AS album_name
FROM user a 
WHERE (a.fname LIKE '%man%' OR a.lname like '%man%')
AND a.status = 'active' 
ORDER BY
    image desc,a.id 
LIMIT 0,30