Subquery in Subquery

thomasuebi

Benutzer
Beiträge
5
Hallo Community,

ich stehe vor einem (für mich als SQL-Amateur) eher komplexeren Problem. Ich versuche mit folgender Abfrage eine Datenbank nach regionalen Einträge zu durchsuchen, wobei die verschiedenen Suchkriterien verschiedene Gewichtung in die Reihenfolge der Listung haben. - typisches Scoring-Prinzip.

Code:
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

Es wird mir jedoch folgende Fehlermeldung zurückgegeben: "Unbekanntes Tabellenfeld 'off1.id' in where clause"

Die Query ist mehrfach verschachtelt, kann ich auf Top-Level-Attribute in Subquerys nicht zugreifen?
 
Werbung:
doch, geht. Zumindest in PG:

Code:
test=*# select * from l1;
 a | b
---+---
 1 | 1
 2 | 2
(2 rows)

test=*# select * from l2;
 c | d
---+---
 1 | 1
 1 | 1
(2 rows)

test=*# select l1.*, case when 1 in (select d from l2 where c=l1.b) then 100 else 0 end from l1;
 a | b | case
---+---+------
 1 | 1 |  100
 2 | 2 |    0
(2 rows)
 
Hallo Community,
Es wird mir jedoch folgende Fehlermeldung zurückgegeben: "Unbekanntes Tabellenfeld 'off1.id' in where clause"

Die Query ist mehrfach verschachtelt, kann ich auf Top-Level-Attribute in Subquerys nicht zugreifen?

Vielleicht benötigt MySQL dort den vollen db.tabelle.feldname Namespace zum vergleichen

z.B so:
testdb.off1.id
 
Sorry für die späte Antwort, habe die letzten Tage versucht einen Workaround zu finden. Wir verwenden als Datenbanksystem Amazon AWS Aurora und die arbeiten offensichtlich mit einem MySQL 5.7.* Syntax.
csc
Habt ihr irgendeine Idee, wie man dieses Problem umgehen könnte?
 
Werbung:
Ich habe etwa probiert, die zweite Subquery in eine Function outzusourcen.
Gibt aber leider Fehler aus:
Code:
#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei '' in Zeile 5

Code:
CREATE FUNCTION func01(offerId int)
RETURNS DECIMAL(10,2)
BEGIN
  SET @output := 0;
  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 = 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
 
Zuletzt bearbeitet:
Zurück
Oben