2

UPDATE

now I can make it work by remove ORDER BY... not sure that's the right thing to do but have to.

First of all,This is all fields(columns) in all table I want to join in the database

enter image description here

and the field in highlighted is the field i want to select.

So, I wrote sql syntax to join all table like this

SELECT country_policies.policyname,
       country_policies.countryname,
       section_data_structure.data_name,
       section_data_content_p.dropname,
       section_data_content_p.comment,
       date_format(section_data_content_p.start_date,'%Y-%m-%d') as start_date,
       date_format(section_data_content_p.end_date,'%Y-%m-%d') as end_date,
       section_data_content_p.policy_id,
       section_data_content_p.country_id,
       sections_content.section_id,
       sections_content.statecode
FROM
(
    (country_policies
     INNER JOIN section_data_content_p
         ON country_policies.policyid = section_data_content_p.policy_id AND
            country_policies.countryid = section_data_content_p.country_id
    )
    INNER JOIN section_data_structure
        ON section_data_content_p.data_structure_id = section_data_structure.id
    INNER JOIN sections_content
        ON section_data_content_p.state_id = sections_content.statecode
)
order by section_data_content_p.dropname;

then I got this error

ER_TOO_BIG_SELECT: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

So I tried to add SET SQL_BIG_SELECTS= 1 ; to the top like this

SET SQL_BIG_SELECTS= 1 ;
SELECT country_policies.policyname, country_policies.countryname, section_data_structure.data_name, section_data_content_p.dropname, section_data_content_p.comment, date_format(section_data_content_p.start_date,'%Y-%m-%d') as start_date, date_format(section_data_content_p.end_date,'%Y-%m-%d') as end_date,section_data_content_p.policy_id,section_data_content_p.country_id,sections_content.section_id,sections_content.statecode
FROM  ((country_policies
INNER JOIN section_data_content_p ON country_policies.policyid = section_data_content_p.policy_id AND country_policies.countryid = section_data_content_p.country_id)
INNER JOIN section_data_structure ON section_data_content_p.data_structure_id = section_data_structure.id
INNER JOIN sections_content ON section_data_content_p.state_id = sections_content.statecode) order by section_data_content_p.dropname;

then I got an error again

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET SQL_BIG_SELECTS= 1 SELECT country_policies.policyname, country_policies.co' at line 1

also tried SET SESSION/ SET OPTION but all not work and I still can't solve this. mysql version is 5.6.32

anyone can help? thanks

PTN
  • 427
  • 4
  • 18
  • http://stackoverflow.com/questions/950465/mysql-sql-big-selects – Ankit Agrawal Mar 29 '17 at 06:01
  • How large are these tables? I've never encountered the too-large-for-select error, but I can suggest that you try to rephrase your query to avoid having so many intermediate rows. How many rows do you plan to return? If much smaller than the threshold, then maybe you can rewrite your query. – Tim Biegeleisen Mar 29 '17 at 06:16
  • @TimBiegeleisen I think it would be more than 10,000 rows – PTN Mar 29 '17 at 06:22

2 Answers2

0

I have tidied up some of the bracket usage in your two code samples. The first is now...

SELECT country_policies.policyname,
       country_policies.countryname,
       section_data_structure.data_name,
       section_data_content_p.dropname,
       section_data_content_p.comment,
       DATE_FORMAT( section_data_content_p.start_date,
                    '%Y-%m-%d' ) AS start_date,
       DATE_FORMAT( section_data_content_p.end_date,
                    '%Y-%m-%d' ) AS end_date,
       section_data_content_p.policy_id,
       section_data_content_p.country_id,
       sections_content.section_id,
       sections_content.statecode
FROM country_policies
     INNER JOIN section_data_content_p ON ( country_policies.policyid = section_data_content_p.policy_id AND country_policies.countryid = section_data_content_p.country_id )
     INNER JOIN section_data_structure ON section_data_content_p.data_structure_id = section_data_structure.id
     INNER JOIN sections_content ON section_data_content_p.state_id = sections_content.statecode
ORDER BY section_data_content_p.dropname;

I have also made the setting of SQL_BIG_SELECT a session variable, as per MySQL - SQL_BIG_SELECTS in the altered second piece of code, which is as follows...

SET SESSION SQL_BIG_SELECTS = 1;
SELECT country_policies.policyname,
       country_policies.countryname,
       section_data_structure.data_name,
       section_data_content_p.dropname,
       section_data_content_p.comment,
       DATE_FORMAT( section_data_content_p.start_date,
                    '%Y-%m-%d' ) AS start_date,
       DATE_FORMAT( section_data_content_p.end_date,
                    '%Y-%m-%d' ) AS end_date,
       section_data_content_p.policy_id,
       section_data_content_p.country_id,
       sections_content.section_id,
       sections_content.statecode
FROM country_policies
     INNER JOIN section_data_content_p ON ( country_policies.policyid = section_data_content_p.policy_id AND country_policies.countryid = section_data_content_p.country_id )
     INNER JOIN section_data_structure ON section_data_content_p.data_structure_id = section_data_structure.id
     INNER JOIN sections_content ON section_data_content_p.state_id = sections_content.statecode
ORDER BY section_data_content_p.dropname;

Note : I have also altered the layout to a style that I find much easier to debug in.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Community
  • 1
  • 1
toonice
  • 2,211
  • 1
  • 13
  • 20
0

SET max_join_size=18446744073709551615 then you can lose big select.

adiga
  • 34,372
  • 9
  • 61
  • 83
Ernest Allen Buffington
  • 92
  • 1
  • 10