CODEWITHSUNDEEP
phpmysqlsql
CIRCLE
CIRCLE

Reputation: 4879

MySQL - join elements from another table with limit 1

I have this table page_base where I want to join only one image in position = 1 from another table called page_base_images ON page_base.id = page_base_images.page_base_id.

page_base [table]

+----+---------+------------+
| id | menu_id | submenu_id |
+----+---------+------------+
|  1 |       1 |          1 |
|  2 |       1 |          2 |
|  3 |       1 |          3 |
+----+---------+------------+

page_base_images [table]

+----+--------------+------------+----------+--------+
| id | page_base_id |   image    | position | active |
+----+--------------+------------+----------+--------+
|  1 |            1 | 01_001.jpg |        1 |      1 |
|  2 |            1 | 01_002.jpg |        2 |      1 |
|  3 |            1 | 01_003.jpg |        3 |      1 |
|  4 |            1 | 01_004.jpg |        4 |      1 |
|  5 |            1 | 01_005.jpg |        5 |      1 |
|  6 |            1 | 01_006.jpg |        6 |      1 |
|  7 |            1 | 01_007.jpg |        7 |      1 |
|  8 |            1 | 01_008.jpg |        8 |      1 |
|  9 |            1 | 01_009.jpg |        9 |      1 |
| 10 |            1 | 01_010.jpg |       10 |      1 |
| 11 |            3 | 03_001.jpg |        1 |      1 |
| 12 |            3 | 03_002.jpg |        2 |      1 |
| 13 |            3 | 03_003.jpg |        3 |      1 |
+----+--------------+------------+----------+--------+

desired array result

+----------+--------------+----------+----------+-------------+------------+
| array_id | page_base_id |  title   | subtitle | description |   image    |
+----------+--------------+----------+----------+-------------+------------+
|        0 |            1 | *string* | *string* | *string*    | 01_001.jpg |
|        1 |            3 | *string* | *string* | *string*    | 03_001.jpg |
+----------+--------------+----------+----------+-------------+------------+

So far I have this but it is always adding the image with id = 1 and if I put a WHERE clause it gives me errors. 

    $page_base_table = get_raw_query("  
    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 image.page_base_id, image.image FROM page_base_images image LIMIT 1) 
    base_image ON base.id = base_image.page_base_id 
    WHERE base_lang.title LIKE '%$string%' ");

Can someone help me limiting the page_base_images join to 1 and grab the image with position = 1?

Upvotes: 1

Views: 71

Answers (4)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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%

Upvotes: 1

RoyB
RoyB

Reputation: 3164

You could also start your select from the page_base_images then left join the languages and left join base on to your result, with a distinct on your page_base_images.page_id column.

That would give you what your looking for, and sounds more logical and error safe than other solutions.

Assuming that image_position is always unique, and that image_position 1 exists is not safe enough in my opinion. 

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

Upvotes: 0

Romain
Romain

Reputation: 51

From what I understand you will never have two images with position=1 in your page_base_images table. Have you tried something like this:

$page_base_table = get_raw_query("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 image AS base_image ON base.id = base_image.page_base_id AND base_image.position=1 WHERE base_lang.title LIKE '%$string%' ");

Upvotes: 0

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Remove the subquery and try this:

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 
  ON base.id = page_base_images.page_base_id 
  AND page_base_images.position = 1 
WHERE base_lang.title LIKE '%$string%'

Upvotes: 0

Related Questions