MySQL Forums
Forum List  »  General

Subquery within subquery - access top level attribute
Posted by: Thomas Übellacker
Date: June 23, 2019 07:29AM

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

Options: ReplyQuote


Subject
Written By
Posted
Subquery within subquery - access top level attribute
June 23, 2019 07:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.