Subquery within subquery - access top level attribute

[Thomas Übellacker]

Good evening, community,

I'm facing a (for me as an SQL amateur) rather complex problem. I try to search a database for regional entries using the following query, whereby the different search criteria have different weighting in the order of listing. - a typical scoring principle.

SELECT
  off1.*,
  (
    CASE WHEN off1.title LIKE '%tes%' THEN 100 WHEN vendors.NAME LIKE '%tes%' THEN 50 WHEN 1 IN (
      SELECT
        category_offer_links.category
      FROM
        category_offer_links
        LEFT JOIN categories ON categories.id = category_offer_links.category
      WHERE
        categories.title LIKE '%tes%'
    ) THEN 50 WHEN off1.description LIKE '%tes%' THEN 40 ELSE 0 END
  ) AS _matchesTextSearch,
  (
    CASE WHEN 1 IN (
      SELECT
        category_offer_links.category
      FROM
        category_offer_links
      WHERE
        category_offer_links.offer = off1.id
    ) THEN 100 ELSE 0 END
  ) AS _matchesCategorySearch,
  (
    SELECT
      (
        100 / (
          Avg(distance) * 3
        )
      )
    FROM
      (
        SELECT
          (
            6371 * Acos(
              Cos(
                Radians('48')
              ) * Cos(
                Radians(locations.latitude)
              ) * Cos(
                Radians(locations.longitude) - Radians('18')
              ) + Sin(
                Radians('48')
              ) * Sin(
                Radians(locations.latitude)
              )
            )
          ) AS distance
        FROM
          events
          LEFT JOIN venues ON events.venue = venues.id
          LEFT JOIN locations ON venues.location = locations.id
        WHERE
          events.offer = off1.id
      ) AS distance
  ) AS _matchesLocationSearch
FROM
  offers AS off1
  LEFT JOIN vendors ON vendors.id = off1.vendor
ORDER BY
  (
    _matchestextsearch + _matchescategorysearch + _matcheslocationsearch
  ) DESC

Throwing error message:

Unknown column: 'off1.id' in where clause

The query is nested multiple times, can not I access top-level attributes in subqueries?

In MySQL 8 it's working just fine, but we're using Amazon AWS Aurora with MySQL 5.6.

Any idea for a working work-around?

I just tried to outsource the sub-subquery in a function as following:

CREATE FUNCTION func01(offerId int) RETURNS DECIMAL(10, 2) BEGIN 
DECLARE @output double;
SELECT 
  @output := (
    100 / (
      Avg(distance) * 3
    ) 
  )
FROM 
  (
    SELECT 
      (
        6371 * Acos(
          Cos(
            Radians('48')
          ) * Cos(
            Radians(locations.latitude)
          ) * Cos(
            Radians(locations.longitude) - Radians('18')
          ) + Sin(
            Radians('48')
          ) * Sin(
            Radians(locations.latitude)
          )
        )
      ) AS distance 
    FROM 
      events 
      LEFT JOIN venues ON events.venue = venues.id 
      LEFT JOIN locations ON venues.location = locations.id 
    WHERE 
      events.offer = offerId
  ) RETURN @output;
END 
SELECT 
  off1.*, 
  (
    CASE WHEN off1.title LIKE '%tes%' THEN 100 WHEN vendors.NAME LIKE '%tes%' THEN 50 WHEN 1 IN (
      SELECT 
        category_offer_links.category 
      FROM 
        category_offer_links 
        LEFT JOIN categories ON categories.id = category_offer_links.category 
      WHERE 
        categories.title LIKE '%tes%'
    ) THEN 50 WHEN off1.description LIKE '%tes%' THEN 40 ELSE 0 END
  ) AS _matchesTextSearch, 
  (
    CASE WHEN 1 IN (
      SELECT 
        category_offer_links.category 
      FROM 
        category_offer_links 
      WHERE 
        category_offer_links.offer = off1.id
    ) THEN 100 ELSE 0 END
  ) AS _matchesCategorySearch, 
  (
    func01(off1.id)
  ) AS _matchesLocationSearch 
FROM 
  offers AS off1 
  LEFT JOIN vendors ON vendors.id = off1.vendor 
ORDER BY 
  (
    _matchestextsearch + _matchescategorysearch + _matcheslocationsearch
  ) DESC

facing following error message:

You have an error in your SQL syntax; it seems the error is around: '@output double; SELECT @output: = (100 / (Avg(distance) * 3)) FROM ( SEL' at line 1