CODEWITHSUNDEEP
mysqlsql
fatnjazzy
fatnjazzy

Reputation: 6152

Mysql query: Select with joining first or default

I have 2 tables. Persons and Images.

One person may have many images.
I want to select a person with his primary image BUT(!), if none of the images marked as isPrimary=1 bring the first one. Only one or less images may be isPrimary=1

SELECT
  *,
  (
    SELECT id 
    FROM Image 
    WHERES personId=r.id AND isPrimary=1  
    LIMIT 1
  ) AS primaryImageId
FROM Persons r 
ORDER BY id DESC;

I did it with subselect, join is also good... Thanks

Upvotes: 3

Views: 473

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

You can use:

SELECT r.*,
       (SELECT i.id
        FROM Image i
        WHERES i.personId = r.id 
        ORDER BY i.isPrimary DESC, i.id ASC
        LIMIT 1
       ) AS primaryImageId
FROM Persons r
ORDER BY id DESC;

This orders the images, with the primary one first -- and then takes the first image.

You should learn to qualify all column references -- this is especially important when using correlated subqueries. I assume that the alias r makes sense on the persons table in your native language. Otherwise, use something sensible such as p.

Upvotes: 3

Related Questions