Problem with MySql query - combining left and inner join

Databases
1

Hi!

New to the site, my first programming forum!:slight_smile: Sorry if I’m not expressing myself clear below, it’s my first time…:rolleyes:

I have a problem with a MySql query. I want to join three tables;

Query A) table1 and table 2 should be INNER JOINed with certain conditions.
Query B) table3 should then be LEFT JOINed with the results from the join above.

In my example, when running query A, I get 38 results. When I then do the combined query with B, I get the the #1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; - message.

Here’s my query:

SELECT *
FROM (
table1 a
JOIN table2 b ON a.league_id = b.league_id
AND (a.home = 'Roger' OR a.away = 'Roger'
)
AND b.season = 1
AND b.abb = 'AA'
)
LEFT JOIN table3 c ON a.index = c.match_index

If this query was running successful, I would get 38 rows. If I change the LEFT JOIN to a regular JOIN (INNER JOIN), I get 32 rows.

So the result from query A above have 6 records (a.index) that don’t match those in table3 (c.match_index). But I want all the rows from query A, so the 6 un-matched ones in table3 should be NULL values.

The column c.match_index doesn’t contain any duplicates.

What am I doing wrong here? If I change the last condition (query B) to something that doesn’t match at all;

LEFT JOIN table3 c ON a.index = c.index

I will get my 38 rows, but of course only NULL values from table3.

Again, sorry if this is an incorrect way of showing a problem.

Thanks.

2 

SELECT *
FROM table1 a
INNER JOIN table2 b 
ON a.league_id = b.league_id
LEFT JOIN table3 c 
ON a.index = c.match_index
WHERE (a.home = 'Roger' OR a.away = 'Roger')
AND   b.season = 1
AND   b.abb = 'AA'
3

Thanks a lot! But unfortunately it’s the same error message (#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows)

Is something wrong with my table? The data type in my c.match_index is a regular INT, do I have to have it in UNIQUE or something?

Thanks.