Re: Subquery within subquery - access top level attribute

[Thomas Übellacker]

Hi, I tried to put a LEFT JOIN in the mid-level subquery, without success:

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 = off2.id
      ) AS distance
      LEFT JOIN offers off2 ON off2.id = off1.id
  ) AS _matchesLocationSearch
FROM
  offers AS off1
  LEFT JOIN vendors ON vendors.id = off1.vendor
ORDER BY
  (
    _matchestextsearch + _matchescategorysearch + _matcheslocationsearch
  ) DESC

Look at off2.

It says: "Unknown column 'off2.id' in where clause"