MySql LIMIT + JOIN is stopping data from being returned?

Question

I originally posted this question on stackoverflow, but then remembered there's a stackexchange specifically for databases!

I've got two tables and am trying to return all profiles that are assigned to a specific cinemaId.

1. content_content
   • Holds data about content_profile (associated

Table:

+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | int(11)      | NO   | PRI | NULL    | auto_increment |
| title                | varchar(200) | NO   |     | NULL    |                |
| slug                 | varchar(100) | NO   | MUL | NULL    |                |
| author_id            | int(11)      | YES  | MUL | NULL    |                |
| descr                | longtext     | NO   |     | NULL    |                |            |
| layout_type          | varchar(32)  | NO   |     |         |                |             |
| is_feature           | tinyint(1)   | NO   |     | NULL    |                |
| main_feature         | tinyint(1)   | NO   |     | NULL    |                |
| postcode_id          | int(11)      | YES  | MUL | NULL    |                |
| cinemaId             | int(11)      | YES  |     | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+

2. content_profile
   • Holds specific data about a profile (think of a profile as a store with opening ours and etc...)

Table:

+------------------------+----------------------+------+-----+---------+-------+
| Field                  | Type                 | Null | Key | Default | Extra |
+------------------------+----------------------+------+-----+---------+-------+
| content_ptr_id         | int(11)              | NO   | PRI | NULL    |       |
| body                   | longtext             | NO   |     | NULL    |       |
| web_site               | varchar(200)         | NO   |     | NULL    |       |
| email                  | varchar(75)          | NO   |     | NULL    |       |   |
| hours                  | longtext             | NO   |     | NULL    |       |
| price_range            | smallint(5) unsigned | YES  | MUL | NULL    |       | |
| primary_category_id    | int(11)              | NO   |     | NULL    |       |   |
+------------------------+----------------------+------+-----+---------+-------+

What I'm trying to do is select * content_profile where content_content.cinemaId = 2.

I've built the following query, which joins the two tables together as well as an content_image table, this table holds 0-many images for a single content_profile.content_ptr_id. Note I've left the images table out of this because it doesn't really have anything todo with the question.

select 
    'profile'.'content_ptr_id' AS 'profile.content_ptr_id',
    'profile'.'body' AS 'profile.body',
    'profile'.'web_site' AS 'profile.web_site',
    'profile'.'email' AS 'profile.email',
    'profile'.'hours' AS 'profile.hours',
    'profile'.'price_range' AS 'profile.price_range',
    'profile'.'price_range_high' AS 'profile.show_in_directory',
    'image'.'id' AS 'image.id',
    'image'.'content_id' AS 'image.content_id',
    'image'.'type' AS 'image.type',
    'image'.'order' AS 'image.order',
    'image'.'caption' AS 'image.caption',
    'image'.'author_id' AS 'image.author_id',
    'image'.'image' AS 'image.image',
    'image'.'link_url' AS 'image.link_url',
    'content'.'id' AS 'content.id',
    'content'.'title' AS 'content.title',
    'content'.'slug' AS 'content.slug',
    'content'.'date' AS 'content.date',
    'content'.'section' AS 'content.section',
    'content'.'author_id' AS 'content.author_id',
    'content'.'descr' AS 'content.descr',
    'content'.'inline_gallery' AS 'content.inline_gallery',
    'content'.'layout_type' AS 'content.layout_type',
    'content'.'blog_id' AS 'content.main_feature',
    'content'.'miffCinemaId' AS 'content.cinemaId'
from 
    ( select 
        'content_profile'.'content_ptr_id',
        'content_profile'.'body',
        'content_profile'.'web_site',
        'content_profile'.'email',
        'content_profile'.'hours',
        'content_profile'.'price_range',
        'content_profile'.'price_range_high',
        'content_profile'.'primary_category_id',
      FROM content_profile 
      LIMIT 10 OFFSET 0
    ) AS profile  
  LEFT JOIN 'content_content' AS 'content' 
    ON 'profile'.'content_ptr_id' = 'content'.'id'  
  LEFT JOIN 'content_image' AS 'image' 
    ON 'profile'.'content_ptr_id' = 'image'.'content_id'  
WHERE 
    content.cinemaId  =  '2' 
order by 
    profile.content_ptr_id ASC, 
    content.date DESC

I've narrowed my problem down to this line, which is inside my subquery:

FROM content_profile LIMIT 10 OFFSET 0

For some reason the LIMIT 10 OFFSET 0 is stopping anything from being returned...If I remove these two fields, all the data is returned. Note their are only 3-5 fields with cinemaId = 2...So limit 0, 10 should 100% show something right? I also noticed if I add LIMIT 12000, it will return the correct data. The highest ID being returned is 117174 (I didn't think LIMIT 10 OFFSET 10 would affect this?)

If there's any information that would help solve this, let me know! I'll try provide it.

Edit:

If I put the limit 10 offset 0 at the end of my query, I will only receive the data from 1 profile, depending on how many content_image's are associated to it.

The reason I have the limit 10 offset 0 inside my subquery, is so I get results for the first 10 content_profile's

Edit 2:

Note I cannot use the GROUP BY clause because of the following errorL

{ [Error: ER_WRONG_FIELD_WITH_GROUP: Expression #12 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'image.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]
  code: 'ER_WRONG_FIELD_WITH_GROUP',
  errno: 1055,
  sqlState: '42000',
  index: 0 }

Answer 1

You have WHERE content.cinemaId = '2' but content is LEFT-JOINED to the profile so it may contain NULLs when no rows exist matching profile.content_ptr_id = content.id and NULL <> '2'.

So you get "first" 10 profiles (without an ORDER BY inside the subquery you may get "random" 10 in reality) and none of these has any matching content so WHERE is not satisfied for any of them and you get empty result.

If you want to receive these rows with NULLs from the left join, you have to move the condition from WHERE to ON:

...
LEFT JOIN content_content AS content 
    ON profile.content_ptr_id = content.id
        AND content.cinemaId  =  '2'
...