title: MySQL select last image in LEFT JOIN
body: <p>I have this function that lists the first inserted image and other data from accommodation database. </p>
  
  <pre><code>function getLIST($sort_by,$saved=0,$filter)
  {
      $session = JFactory::getSession();
      $lang =&amp; JFactory::getLanguage();
  
      $query = "SELECT a.id FROM #__jomholiday_fields AS a where a.name='COM_JOMHOLIDAY_ACCOMODATION_RATING'";
      $db = $this-&gt;getDbo();
      $db-&gt;setQuery( $query );
      $rating_id = $db-&gt;loadResult();
  
      $query = "SELECT a.id FROM #__jomholiday_fields AS a where a.name='COM_JOMHOLIDAY_BOARD_BASIS'";
      $db-&gt;setQuery( $query );
      $board_basis_id = $db-&gt;loadResult();
  
      $query  = $db-&gt;getQuery(true);
  
      $listing_expiry_days=(int)$this-&gt;getConf("listing_expiry_days");
      if($listing_expiry_days)
      {
          $now=strtotime("now");
          $temp=$now-$listing_expiry_days*(60*60*12);
          $toold=date('Y-m-d',$temp);
      }
      else $toold=0;
      $search_mod=0;
  
      $query-&gt;select(
          $this-&gt;getState(
              'list.select',
              'a.id, a.categories_id, a.number, a.headline, a.short_description, a.lat, a.lon, a.published, a.dpublished' .
              ', a.featured, a.address_name, a.created, a.modified, p.name , a.price_night, a.price_week, a.sleeps'
          )
      );
  
      $query-&gt;from('#__jomholiday_listings AS a');
  
      $query-&gt;select('p.name AS image');
      $query-&gt;select('MAX(p.id)');
      $query-&gt;join('LEFT', '#__jomholiday_photos AS p ON p.listings_id = a.id AND (p.rooms_id=0 OR p.rooms_id IS NULL)  ');
  
      $query-&gt;select('ac.name AS category_name');
      $query-&gt;select('ac.rental AS price_type');
      $query-&gt;join('LEFT', '#__jomholiday_categories AS ac ON ac.id = a.categories_id');
      $query-&gt;where("(ac.lang='".$lang-&gt;getTag()."' OR ac.lang='*')");
      //$query-&gt;where("p.id = MAX(p.id)");
      $query-&gt;select('AVG(r.rating) AS rating_average');
      $query-&gt;join('LEFT', '#__jomholiday_reviews AS r ON r.published="1" AND r.listingid=a.id');
  
      if ($rating_id){    
          $query-&gt;select('f1.value AS accomodation_rating');
          $query-&gt;join('LEFT', '#__jomholiday_listings_to_fields AS f1 ON f1.listings_id = a.id AND f1.fields_id='.$rating_id.'');
      }
      if ($board_basis_id){   
          $query-&gt;select('f2.value AS all_inclusive');
          $query-&gt;join('LEFT', '#__jomholiday_listings_to_fields AS f2 ON f2.listings_id = a.id AND f2.fields_id='.$board_basis_id.' and f2.value="COM_JOMHOLIDAY_ALL_INCLUSIVE"');
  
          $query-&gt;select('f3.value AS bed_breakfast');
          $query-&gt;join('LEFT', '#__jomholiday_listings_to_fields AS f3 ON f3.listings_id = a.id AND f3.fields_id='.$board_basis_id.' and f3.value="COM_JOMHOLIDAY_BED_BREAKFAST"');
  
          $query-&gt;select('f4.value AS full_board');
          $query-&gt;join('LEFT', '#__jomholiday_listings_to_fields AS f4 ON f4.listings_id = a.id AND f4.fields_id='.$board_basis_id.' and f4.value="COM_JOMHOLIDAY_FULL_BOARD"');
  
          $query-&gt;select('f5.value AS half_board');
          $query-&gt;join('LEFT', '#__jomholiday_listings_to_fields AS f5 ON f5.listings_id = a.id AND f5.fields_id='.$board_basis_id.' and f5.value="COM_JOMHOLIDAY_HALF_BOARD"');
  
          $query-&gt;select('f6.value AS room_only');
          $query-&gt;join('LEFT', '#__jomholiday_listings_to_fields AS f6 ON f6.listings_id = a.id AND f6.fields_id='.$board_basis_id.' and f6.value="COM_JOMHOLIDAY_ROOM_ONLY"');
  
          $query-&gt;select('f7.value AS self_cattering');
          $query-&gt;join('LEFT', '#__jomholiday_listings_to_fields AS f7 ON f7.listings_id = a.id AND f7.fields_id='.$board_basis_id.' and f7.value="COM_JOMHOLIDAY_SELF_CATTERING"');
  
      }
      if ($saved)
      {
          $cids=JRequest::getString('savedlist', '', 'cookie');
          $cid=explode(",",$cids);
          JArrayHelper::toInteger($cid);
          $cids=implode(",",$cid);
          $query-&gt;where('a.id IN ( '. $cids .' )');
          $search_mod=1;
      }
  
      $query-&gt;where("a.published='1'");
      $query-&gt;where("a.approved='1'");
      if ($toold) $query-&gt;where("a.dpublished&gt;'$toold'");
  
      if ($session-&gt;get('jom_zlat')!=null)
      {
          $lat1=(float)$session-&gt;get('jom_lat')-$session-&gt;get('jom_zlat');
          $lat2=(float)$session-&gt;get('jom_lat')+$session-&gt;get('jom_zlat');
          $lon1=(float)$session-&gt;get('jom_lon')-$session-&gt;get('jom_zlon');
          $lon2=(float)$session-&gt;get('jom_lon')+$session-&gt;get('jom_zlon');
          if ($lat1&gt;$lat2){$temp=$lat1;$lat1=$lat2;$lat2=$temp;}
          if ($lon1&gt;$lon2){$temp=$lon1;$lat1=$lon2;$lon2=$temp;}
          if ($lat1) $query-&gt;where("a.lat&gt;".$lat1);
          if ($lat2) $query-&gt;where("a.lat&lt;".$lat2);
          if ($lon1) $query-&gt;where("a.lon&gt;".$lon1);
          if ($lon2) $query-&gt;where("a.lon&lt;".$lon2);
          $search_mod=1;
      }
      if ($session-&gt;get('jom_address_select')!=null)
      {
          $temp=explode(",",$session-&gt;get('jom_address_select'));
          if ($temp[0]) $temp1=$this-&gt;getRegion((int)$temp[0]);
          if ($temp[1]) $temp1.=", ".$this-&gt;getRegion((int)$temp[1]);
          if ($temp[2]) $temp1.=", ".$this-&gt;getRegion((int)$temp[2]);
          $query-&gt;where("(a.address_name like '%".str_replace("'","\'",$temp1)."%')", ENT_QUOTES);
          $search_mod=1;
      }
      if (str_replace(",","",$session-&gt;get('jom_booking')))
      {
          $temp=explode(",",$session-&gt;get('jom_booking'));
          if ($temp[0] &amp;&amp; $temp[2]&gt;0)
          {
              $in=strtotime($temp[0]);
              $out=strtotime($temp[1]);
              $query-&gt;where("a.sleeps &gt;= '".(int)$temp[2]."'");
              while ($in&lt;$out)
              {
                  $query-&gt;where("(a.booking LIKE '%,".date("ymd",$in)."-".(int)$temp[2].",%' OR a.booking NOT LIKE '%,".date("ymd",$in)."-%' OR a.booking IS NULL)");
                  $in=strtotime('+1 day', $in);
              }
          }
          $search_mod=1;
      }
      if ((int)$session-&gt;get('jomholiday_search_cat'))
      {
          $temp=explode(",",$session-&gt;get('jomholiday_search_cat'));
          JArrayHelper::toInteger($temp);
          $cidss = implode( ',', $temp );
          $query-&gt;where("a.categories_id IN ( ". $cidss ." )");
          $search_mod=1;
      }
      if ($session-&gt;get('jom_title')!=null){$search_mod=1; $query-&gt;where("(a.headline like '%".str_replace("'","\'",$session-&gt;get('jom_title'))."%' OR a.address_name like '%".str_replace("'","\'",$session-&gt;get('jom_title'))."%')", ENT_QUOTES);}
      if ((int)$session-&gt;get('jom_pricemin')!=null){$search_mod=1; $query-&gt;where("a.price_night&gt;=".(int)$session-&gt;get('jom_pricemin'));}
      if ((int)$session-&gt;get('jom_pricemax')!=null) {$search_mod=1;$query-&gt;where("a.price_night&lt;=".(int)$session-&gt;get('jom_pricemax'));}
  
      if ($session-&gt;get('jomholiday_search'))
      {
          $val=json_decode($session-&gt;get('jomholiday_search'),true);
          if (is_array($val))
          {
              $array_keys = array_keys($val);
              foreach($array_keys as $key) if ($val[$key]!=null)
              {
                      if (is_array($val[$key]))
                      {
                          $array_keys1 = array_keys($val[$key]);
                          foreach($array_keys1 as $key1)
                          {
                              $val[$key][$key1]=htmlspecialchars($val[$key][$key1],ENT_QUOTES);
                              $query-&gt;where('exists(select 1 from #__jomholiday_listings_to_fields AS fs'.$key.'_'.$key1.' where fs'.$key.'_'.$key1.'.listings_id = a.id AND fs'.$key.'_'.$key1.'.value="'.$val[$key][$key1].'" and fs'.$key.'_'.$key1.'.fields_id='.$key.')');
                          }
                      }
                      else if (trim($val[$key])!=null)
                      {
                          $val[$key]=htmlspecialchars(trim($val[$key]),ENT_QUOTES);
                          $query-&gt;where('exists(select 1 from #__jomholiday_listings_to_fields AS fs'.$key.' where fs'.$key.'.listings_id = a.id AND fs'.$key.'.value="'.$val[$key].'" and fs'.$key.'.fields_id='.$key.')');
                      }
              }
              $search_mod=1;
          }
      }
  
      if ((int)JRequest::getVar('cid') &amp;&amp; !$search_mod) $query-&gt;where("a.categories_id='".(int)JRequest::getVar('cid')."'");
  
      if ((int)$session-&gt;get('jom_pricemin_f')!=null) $query-&gt;where("a.price_night&gt;=".(int)$session-&gt;get('jom_pricemin_f'));
      if ((int)$session-&gt;get('jom_pricemax_f')!=null) $query-&gt;where("a.price_night&lt;=".(int)$session-&gt;get('jom_pricemax_f'));
  
      if ($session-&gt;get('jomholiday_filter'))
      {
          $val=json_decode($session-&gt;get('jomholiday_filter'),true);
          if (is_array($val))
          {
              $array_keys = array_keys($val);
              foreach($array_keys as $key) if ($val[$key]!=null)
              {
                      if (is_array($val[$key]))
                      {
                          $array_keys1 = array_keys($val[$key]);
                          foreach($array_keys1 as $key1)
                          {
                              $val[$key][$key1]=htmlspecialchars($val[$key][$key1],ENT_QUOTES);
                              $query-&gt;where('exists(select 1 from #__jomholiday_listings_to_fields AS fsf'.$key.'_'.$key1.' where fsf'.$key.'_'.$key1.'.listings_id = a.id AND fsf'.$key.'_'.$key1.'.value="'.$val[$key][$key1].'" and fsf'.$key.'_'.$key1.'.fields_id='.$key.')');
                          }
                      }
                      else if (trim($val[$key])!=null)
                      {
                          $val[$key]=htmlspecialchars(trim($val[$key]),ENT_QUOTES);
                          $query-&gt;where('exists(select 1 from #__jomholiday_listings_to_fields AS fsf'.$key.' where fsf'.$key.'.listings_id = a.id AND fsf'.$key.'.value="'.$val[$key].'" and fsf'.$key.'.fields_id='.$key.')');
                      }
              }
          }
      }
      if ($session-&gt;get('jom_address')!=null) $query-&gt;where("a.address_name like '".str_replace("'","\'",$session-&gt;get('jom_address'))."%'", ENT_QUOTES);
  
      $query-&gt;group("a.id");
      if ($this-&gt;getState('sort_by')=="a.created DESC") $order_s=$this-&gt;getState('sort_by');
      elseif ($this-&gt;getState('sort_by')=="a.modified DESC") $order_s=$this-&gt;getState('sort_by');
      elseif ($this-&gt;getState('sort_by')=="a.price_night DESC") $order_s=$this-&gt;getState('sort_by');
      elseif ($this-&gt;getState('sort_by')=="a.price_night") $order_s=$this-&gt;getState('sort_by');
      elseif ($this-&gt;getState('sort_by')=="a.headline") $order_s=$this-&gt;getState('sort_by');
      else $order_s="";
      if ($this-&gt;getConf(premium_first))
      {
          if ($order_s &amp;&amp; JRequest::getVar('task')!='rss') $query-&gt;order("a.featured desc, ".$order_s.",id desc");
          else $query-&gt;order("a.featured desc, a.created DESC, id desc");
      }
      else
      {
  
          if ($order_s &amp;&amp; JRequest::getVar('task')!='rss') $query-&gt;order($order_s.",id desc");
          else $query-&gt;order("a.created DESC, id desc");
      }
  
      if ($saved || JRequest::getVar('task')=='rss') $data = $this-&gt;_getList($query);
      else $data = $this-&gt;_getList($query, $this-&gt;getState('limitstart'), $this-&gt;getState('limit'));
      //echo $query;
      return $data;   
  }
  </code></pre>
  
  <p>I need to retrive last inserted image from table #__jomholiday_photos. As you can see I did try to put the MAX(p.id) but that didn't helped. 
  I did try to put the $query->order("p.id DESC"); , that didn't helped aether. </p>
  
  <p>Update: 
  Here is a query that is generated with this code</p>
  
  <pre><code>SELECT a.id, a.categories_id, a.number, a.headline, a.short_description, a.lat, a.lon,
   a.published, a.dpublished, a.featured, a.address_name, a.created, a.modified,
   p.name , a.price_night, a.price_week, a.sleeps,p.name AS image,MAX(p.id),ac.name AS category_name,
   ac.rental AS price_type,AVG(r.rating) AS rating_average,f1.value AS accomodation_rating,
   f2.value AS all_inclusive,f3.value AS bed_breakfast,f4.value AS full_board,f5.value AS half_board,
   f6.value AS room_only,f7.value AS self_cattering 
   FROM #__jomholiday_listings AS a 
   LEFT JOIN #__jomholiday_photos AS p ON p.listings_id = a.id AND (p.rooms_id=0 OR p.rooms_id IS NULL)  
   LEFT JOIN #__jomholiday_categories AS ac ON ac.id = a.categories_id 
    LEFT JOIN #__jomholiday_reviews AS r ON r.published="1" AND r.listingid=a.id 
    LEFT JOIN #__jomholiday_listings_to_fields AS f1 ON f1.listings_id = a.id 
    AND f1.fields_id=22
    LEFT JOIN #__jomholiday_listings_to_fields AS f2 ON f2.listings_id = a.id 
    AND f2.fields_id=10 
    and f2.value="COM_JOMHOLIDAY_ALL_INCLUSIVE" LEFT JOIN #__jomholiday_listings_to_fields AS f3 
    ON f3.listings_id = a.id AND f3.fields_id=10 and f3.value="COM_JOMHOLIDAY_BED_BREAKFAST" 
    LEFT JOIN #__jomholiday_listings_to_fields AS f4 ON f4.listings_id = a.id 
    AND f4.fields_id=10 
    and f4.value="COM_JOMHOLIDAY_FULL_BOARD" LEFT JOIN #__jomholiday_listings_to_fields AS f5 
    ON f5.listings_id = a.id AND f5.fields_id=10 and f5.value="COM_JOMHOLIDAY_HALF_BOARD" 
    LEFT JOIN #__jomholiday_listings_to_fields AS f6 ON f6.listings_id = a.id 
     AND f6.fields_id=10 
    and f6.value="COM_JOMHOLIDAY_ROOM_ONLY" LEFT JOIN #__jomholiday_listings_to_fields AS f7 
    ON f7.listings_id = a.id AND f7.fields_id=10 and f7.value="COM_JOMHOLIDAY_SELF_CATTERING" 
    WHERE (ac.lang='hr-HR' OR ac.lang='*') 
    AND a.published='1' AND a.approved='1' 
      GROUP BY a.id 
     ORDER BY a.featured desc,    a.headline,id desc LIMIT 0, 15
  </code></pre>
  
  <p>'p.name' retrieves the firs record from '#__jomholiday_photos'. I need the last one of each listings.  </p>