MySQL nested left join with outer references

Question

Let's say we have these (simplified from a more complex one) example tables:

== st ==    == pr ===   == rn ===  <– tables
sta   pg    pg    rou   sta   rou  <– 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

I wanted to execute this (also simplified) query with an inner left join:

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

to have this result:

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

but MySQL throws up this error: #1054 - Unknown column 'st.sta' in 'on clause'.

I tried to solve this with no success, until somebody posted (and deleted) a nice idea of joining the rn column twice. Thanks to him/her, I was able to create a working solution:

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

While this query works, it requires the duplicate joins in the right order, very fragile, and ugly.

Can you give a cleaner solution, which joins the rn table only once?

Here is the sample database in sql for copypasting, if you want to play with it:

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');

Answer 1

I think I've finally got it:

SELECT st.*, pr.*, rn.*
FROM st
  LEFT JOIN rn ON st.sta = rn.sta
  LEFT JOIN pr ON st.pg  = pr.pg
              AND (rn.rou = pr.rou OR rn.rou IS NULL)

Answer 2

select st.*, pr.*, rn.*  
from st
left join  pr on pr.pg = st.pg
left  join   rn  on rn.sta = st.sta