Mysql: Error Code: 1054. Unknown Column in 'Where Clause'

MYSQL: Error Code: 1054. Unknown column in 'where clause'

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.

Error Code: 1054. Unknown column 'sdate' in 'where clause'

Instead of having the selects in the main select clause I created a subquery to join against so that startDate could be checked

SELECT at.startDate AS sdate, at.dau AS DAU, (DAU/MAU.cnt) as AVG
FROM attendance AS at
JOIN (SELECT startdate, count(distinct uid) as cnt
      FROM (SELECT uid, startdate FROM dsession
            UNION ALL
            SELECT uid, startdate FROM rsession) as ua
      GROUP BY startdate
     ) as MAU ON MAU.startdate = at.startdate

Hopefully I didn't mess anything up when restructuring the query :)

Error Code: 1054. Unknown column 'r.when_added' in 'on clause'

Looking at your query, I doubt that you actually need a join in the subquery. Doesn't this do what you want?

SELECT r.coupon_id, c.who_added, c.coupon_name, r.when_added,
    (
        SELECT COUNT(*) 
        FROM redeemed_coupons r2 
        WHERE r2.coupon_id = c.coupon_id AND r2.when_added = r.when_added
    ) cnt
FROM redeemed_coupons r 
INNER JOIN coupon c ON c.coupon_id = r.coupon_id  
WHERE r.when_added BETWEEN 1602827745 AND 1613084678 AND c.who_added = 1
ORDER BY r.when_added ;

Apart from the changes in the suqbuery, I also re-organized a little the conditions in the outer query; this does not change the result, but I find it more readable to only put the correlation conditions in the ON clause, and other conditions in the WHERE clause.

You could also express this with a window count(), if you are running MySQL 8.0:

SELECT r.coupon_id, c.who_added, c.coupon_name, r.when_added,
    COUNT(*) OVER(PARTITION BY r.coupon_id, r.when_added) cnt
FROM redeemed_coupons r 
INNER JOIN coupon c
    ON  r.coupon_id = c.coupon_id 
    AND c.who_added = 1 
    AND r.when_added BETWEEN 1602827745 AND 1613084678
ORDER BY r.when_added ;

---

Related Topics