Complex: 3 LEFT JOIN not producing results

Databases
1

Hello,

We have a complex SELECT which is to look at 3 Tables and produce a list of members that have not been contacted about a given subject they are interested in, we have tried this SELECT, it is producing No Errors but produces Zero results:

SELECT swi.email, swd.user_id, swd.category FROM sub_w_dat AS swd
LEFT JOIN sub_we_index AS swi ON (swi.id = swd.ix_id)
LEFT JOIN qa_em_hy AS cc ON (cc.email = swi.email) WHERE swd.category IN (158, 38, 27) AND cc.qa_id = 1399 AND cc.email IS NULL ORDER BY RAND() LIMIT 1000;

How to fix this?

Thanks.

2

It’s the last LEFT JOIN.

If cc.email is null, cc.qa_id will also be null because no record is found on the qa_em_hy table (why do you prefix it as cc if there are no c’s in the table name?).

3

what Dave said

and here’s how to fix it – note the ON clause

SELECT swi.email
     , swd.user_id
     , swd.category 
  FROM sub_w_dat AS swd
LEFT OUTER
  JOIN sub_we_index AS swi 
    ON swi.id = swd.ix_id
LEFT OUTER
  JOIN qa_em_hy AS cc 
    ON cc.email = swi.email 
   AND cc.qa_id = 1399 
 WHERE swd.category IN (158, 38, 27) 
   AND cc.email IS NULL 
ORDER 
    BY RAND() LIMIT 1000;
1 Like
4

WOW Perfect. Thanks :slight_smile:
Question: Why do you use LEFT OUTER JOIN rather than just LEFT JOIN?

BTW, this Select without Indexing takes 59 Seconds to produces results and with Indexing it takes 0.042 Sec. Use the Index Luc :slight_smile:

5

Hi Dave,
Thanks for that Superb intel.
We prefix it as cc because it is left from a legacy typeo
Funny you noticed that :slight_smile:

6

thanks for noticing

i do it to emphasize that it’s not the direction, left or right or full, that matters

it’s a forced reminder that it’s an outer join, with all that this implies

7

Aha. Ok Thanks

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.