w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
MySQL - join elements from another table with limit 1

You can rewrite your query as below by adding additional condition in join of page_base_images i.e ON (base.id = base_image.page_base_id AND base_image.position =1)

SELECT 
  base_lang.title,
  base_lang.subtitle,
  base_lang.description,
  base_image.image 
FROM
  page_base base 
  LEFT OUTER JOIN page_base_languages base_lang 
    ON base.id = base_lang.page_base_id 
  LEFT OUTER JOIN  page_base_images AS base_image 
    ON (base.id = base_image.page_base_id AND  base_image.position =1)
WHERE base_lang.title LIKE '%$string%' 

EDIT to get the images which have the minimum position

SELECT 
  base_lang.title,
  base_lang.subtitle,
  base_lang.description,
  base_image.image 
FROM
  page_base base 
  LEFT OUTER JOIN page_base_languages base_lang 
    ON base.id = base_lang.page_base_id 
  LEFT OUTER JOIN  
  (SELECT  page_base_id,image,MIN(`position`) FROM page_base_images GROUP
BY page_base_id)
  AS base_image 
  ON (base.id = base_image.page_base_id)    
WHERE base_lang.title LIKE '%$string%




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.