How do I have an "get only if this exists" in MySQL?

My query...

   SELECT MIN(p.`land_price`) AS `min_price`,
          MAX(p.`land_price`) AS `max_price`,
          p.`estate`,
          m.`file`

     FROM `properties` AS `p`
LEFT JOIN `estates` AS `e`
       ON p.`estate` = e.`title`

LEFT JOIN `media` AS `m`
       ON  m.`category` = e.`id` 

    WHERE p.`estate` IN ("EstateA", "EstateB")
      AND p.`land_price` != 0
 GROUP BY p.`estate

What I want to do, is get 开发者_如何转开发an item from media table (aliased as m) where name = "Profile", except to only select if it exists, otherwise just have blank column for others in result set. At the moment, if I add that constraint, it only gives me matches where that name exists.

At the moment, it is just selecting the first item in media that matches the constraints.

The "WHERE" part limits the results from the main table properties. In this case you should add another constraint to the "ON" part of the second LEFT JOIN.

Example:

SELECT MIN(p.`land_price`) AS `min_price`,
          MAX(p.`land_price`) AS `max_price`,
          p.`estate`,
          m.`file`

     FROM `properties` AS `p`
LEFT JOIN `estates` AS `e`
       ON p.`estate` = e.`title`

LEFT JOIN `media` AS `m`
       ON  m.`category` = e.`id` 
       AND m.`name` = 'Profile'

    WHERE p.`estate` IN ("EstateA", "EstateB")
      AND p.`land_price` != 0
 GROUP BY p.`estate