MySql LIMIT + JOIN is stopping data from being returned

limitsMySQLsubquery

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.

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    |                |
+----------------------+--------------+------+-----+---------+----------------+

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 }

Best Answer

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'
...

Related Solutions

MySQL – Get Column from Multiple Tables

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Mysql – find and insert row to another table using thesql trigger

This should do the trick for you:

DELIMITER $$
DROP TRIGGER IF EXISTS `employee_INSERT` $$
CREATE TRIGGER `employee_INSERT` 
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
    INSERT INTO employee_tools (Id, Tool)
    SELECT new.Id, tools.Tool_Name 
            FROM tools
            WHERE tools.Division = new.Division;
END $$
DELIMITER ;