Subquery in Subquery

[thomasuebi]

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.

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?

 

[akretschmer]

doch, geht. Zumindest in PG:

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)

 

[Dravion]

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

 

[thomasuebi]

@akretschmer danke für die rasche Rückmeldung. Ich verwende MySQL.

@Dravion danke für den Tipp, habe ich versucht - leider schmeißt er mir hier dieselbe Fehlermeldung...

Bin am verzweifeln, sitze seit Vormittag daran

 

[akretschmer]

danke für die rasche Rückmeldung. Ich verwende MySQL.

hast Du mal versucht, das in MySQL nachzuvollziehen?

 

[Dravion]

Schau Dir mal dieses Beispiel an, da kann man die verschiedenen MySQL Versionen im Dropdown umschalten.
Die Query dort macht etwas ganz ähnliches wie Du. Unter MySQL 5.7.x kommt es zu einem Fehler, schaltest Du um auf MySQL 8.x verschindet der Fehler
MySQL 5.7 | db<>fiddle

 

[thomasuebi]

@Dravion wow super danke dir vielmals! Klingt sehr vielversprechend. Ich melde mich morgen nochmal mit der endgültigen Lösung.

 

[thomasuebi]

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?

 

[thomasuebi]

Ich habe etwa probiert, die zweite Subquery in eine Function outzusourcen.
Gibt aber leider Fehler aus:

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei '' in Zeile 5

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