title: MySQL nested left join with outer references
body: <p>Let's say we have these (simplified from a more complex one) example tables:</p>
  
  <pre><code>== st ==    == pr ===   == rn ===  &lt;– tables
  sta   pg    pg    rou   sta   rou  &lt;– fields
  ========    =========   =========
  H1    aa    aa    aaA   H1    aaA
  
  H2    aa    aa    aaB   H2    aaB
  H3    aa                H3    aaB
  
  H4    aa    aa    aaC   H4    aaC
  H5    aa                H5    aaC
  H6    aa                H6    aaC
  
  H7    aa
  
  H8    bb    bb    NULL
  </code></pre>
  
  <p>I wanted to execute this (also simplified) query with an inner left join:</p>
  
  <pre><code>SELECT st.*, pr.*, rn.*
  FROM         st
  INNER JOIN ( pr
  LEFT  JOIN   rn  ON pr.rou = rn.rou
                  AND          rn.sta = st.sta -- ERROR here
             )     ON pr.pg =           st.pg
  -- filter out bad rows
  WHERE ( rn.id )                          -- a: not null
     OR ( pr.rou ='aaC' AND rn.id IS NULL) -- b: no joinable rn found: choose
                                           --    by a predefined pr.rou value
     OR ( pr.rou IS NULL )                 -- c: no need to join
  </code></pre>
  
  <p>to have this result:</p>
  
  <pre><code>== st ==    == pr ===   == rn ===
  sta   pg    pg    rou   sta   rou
  ========    =========   =========
  H1    aa    aa    aaA   H1    aaA
  H2    aa    aa    aaB   H2    aaB
  H3    aa    aa    aaB   H3    aaB
  H4    aa    aa    aaC   H4    aaC
  H5    aa    aa    aaC   H5    aaC
  H6    aa    aa    aaC   H6    aaC
  
  H7    aa    aa    aaA   NULL  NULL \  H7 has no rn, so choose
  H7    aa    aa    aaB   NULL  NULL  } 1 row of these at the
  H7    aa    aa    aaC   NULL  NULL /  WHERE / b condition
  
  H8    bb    bb    NULL  NULL  NULL
  </code></pre>
  
  <p>but MySQL throws up this error: <code>#1054 - Unknown column 'st.sta' in 'on clause'</code>.</p>
  
  <p>I tried to solve this with no success, until somebody posted (and deleted) a nice idea of joining the <code>rn</code> column twice. Thanks to him/her, I was able to create a working solution:</p>
  
  <pre><code>SELECT st.*, pr.*, rn.*, rn2.*
  
  FROM         st
  INNER JOIN   pr         ON st.pg = pr.pg
  LEFT  JOIN   rn         ON st.sta = rn.sta
  LEFT  JOIN   rn AS rn2  ON pr.rou = rn2.rou -- the two rn's join order is important
                         AND rn.id  = rn2.id  -- if first exists, second must match or null, first null =&gt; second null
  WHERE ( rn.id = rn2.id )                    -- a: both not null
     OR ( pr.rou ='aaC' AND rn.id IS NULL)    -- b: no joinable rn found: choose by predefined pr.rou value
     OR ( pr.rou IS NULL )                    -- c: no need to join
  </code></pre>
  
  <p>While this query works, it requires the duplicate joins in the right order, very fragile, and ugly.</p>
  
  <p>Can you give a cleaner solution, which joins the <code>rn</code> table only once?</p>
  
  <p>Here is the sample database in sql for copypasting, if you want to play with it:</p>
  
  <pre><code>DROP    TABLE IF     EXISTS st;
  CREATE  TABLE IF NOT EXISTS st (
    id  int  AUTO_INCREMENT,
    sta varchar(9),
    pg  varchar(9),
    PRIMARY KEY ( id )
  )  AUTO_INCREMENT=1;
  
  DROP    TABLE IF     EXISTS pr;
  CREATE  TABLE IF NOT EXISTS pr (
    id  int  AUTO_INCREMENT,
    pg  varchar(9),
    rou varchar(9),
    PRIMARY KEY ( id )
  )  AUTO_INCREMENT=1;
  
  DROP    TABLE IF     EXISTS rn;
  CREATE  TABLE IF NOT EXISTS rn (
    id  int  AUTO_INCREMENT,
    sta varchar(9),
    rou varchar(9),
    PRIMARY KEY ( id )
  )  AUTO_INCREMENT=1;
  
  
  INSERT INTO st 
  (sta , pg ) VALUES
  ('H1','aa'),
  ('H2','aa'),
  ('H3','aa'),
  ('H4','aa'),
  ('H5','aa'),
  ('H6','aa'),
  ('H7','aa'),
  ('H8','bb');
  INSERT INTO pr
  ( pg , rou ) VALUES
  ('aa','aaA'),
  ('aa','aaB'),
  ('aa','aaC'),
  ('bb', NULL);
  INSERT INTO rn
  (sta , rou ) VALUES
  ('H1','aaA'),
  ('H2','aaB'),
  ('H3','aaB'),
  ('H4','aaC'),
  ('H5','aaC'),
  ('H6','aaC');
  </code></pre>