MYSQL: Error Code: 1054. Unknown column in ‘where clause’

I’m trying to pass a column from the outer query as shown below to the inner query in the WHERE clause and MySQL does not like it. I’m unsure how to rewrite this query to make it work.

The error message I am getting is Unknown column ‘y.DateShipped’ in where clause

What I am trying to do is to join to the row in the inner table with an EffectiveDate that is less than the DateShipped and also is the max EffectiveDate in the inner join (there can be multiple rows for the same group by with different EffectiveDate(s))

I would love to know how to get this working or rewrite it so that it will work. I am using MySQL 5.6, so I don’t have window functions available otherwise I think that could work.

select 
    x.id,
    y.id,
    y.DateShipped 
from Shipment y inner join
    (select id, SourceId, DestinationId, SourcePPFContractId, EffectiveDate 
    from Relationship where EffectiveDate <= y.DateShipped order by 
    EffectiveDate desc limit 1) x 
on x.DestinationId = y.DestinationCustomerId 
and x.SourceId = y.OriginCustomerId 
and x.SourcePPFContractId = y.OriginContractId; 

Answer

You are attempting something called a lateral join — and MySQL does not support those. Because you want only one column, you can use a correlated subquery:

select (select r.id 
        from Relationship r
        where r.DestinationId = s.DestinationCustomerId and
              r.SourceId = s.OriginCustomerId and
              r.SourcePPFContractId = s.OriginContractId and
              r.EffectiveDate <= s.DateShipped
        order by r.EffectiveDate desc
        limit 1
       ) as x_id,
       s.id, s.DateShipped 
from Shipment s ;

Note that I also changed the table aliases to be abbreviations for the table names — so the query is easier to read.