Tabellenabfrage, Kriterien koppeln

[wolf29]

Hi.

Ich brüchte mal einen Gedankenanstoß, wie ich vernünftig an die Geschichte rangehe.

Folgendes Problem: es gibt u.a. eine Tabelle "members_daten", die sämtliche relevanten Informationen der einzelnen User enthält (Haarfarbe, Größe, Adresse usw.). Jetzt wird eine Suche nach unterschiedlichen Kriterien gewünscht. Sprich: wer hat z.B. dunkjle Haar, ist weiblich und kommt aus dem PLZ Gebiet 1xxx. Bisher auch noch kein Problem. Jetzt soll aber nicht nur die User sortiert angezeigt werden, bei denen alles stimmt, sondern auch die, wo z.B. nur 2 von 3 Kriterien stimmen usw.!

Hat jemand eine Idee, wie ich das elegant lösen kann? Ich brauch nur einen Gedankenanstoß!

Wolf29

So noch eine andere Idee. Geht allerdings nur wegen einem bösen SQL-Foul.

SELECT m.*,
 ( SELECT COUNT(*) FROM members_daten m1 WHERE m1.haarfarbe = 2 AND m1.id = m.id) +
 ( SELECT COUNT(*) FROM members_daten m2 WHERE m2.geschlecht = 2 AND m2.id = m.id) +
 ( SELECT COUNT(*) FROM members_daten m3 WHERE m3.plz BETWEEN 65000 AND 65500 AND m3.id = m.id) AS q1
 FROM members_daten m
HAVING q1 >= 2
ORDER BY q1 DESC;

+----+--------+-----------+-------+------------+------+
| id | name   | haarfarbe | plz   | geschlecht | q1   |
+----+--------+-----------+-------+------------+------+
|  2 | Name-2 |         2 | 66000 |          2 |    2 |
|  4 | Name-4 |         2 | 65000 |          1 |    2 |
+----+--------+-----------+-------+------------+------+
2 rows in set (0.00 sec)

mysql>

Grüße
Thomas

[nikosch]

Ich bin nicht sicher, ob das vielleicht mit Variablen ginge (wenn, dann vermutlich aber nur via HAVING).

Ist .. WHEN feld = 1 THEN x .. nicht ebenfalls ein schönes boolsches Prädikat?

Nunja, CASE ist eben von vornherein auf mehrere Bedingungen ausgelegt.

Ja. Und IF ist deutlich prägnanter, wenn es um eine boolsche Bedingungs geht. SO what?

Da läßt sich natürlich trefflich streiten was prägnat ist oder nicht.
Ist .. WHEN feld = 1 THEN x .. nicht ebenfalls ein schönes boolsches Prädikat?

Naja, die ist ja recht naheliegend. Und ORDER ist natürlich möglich:

...
ORDER BY CASE
           WHEN haarfarbe = 2 THEN 1
               ELSE 0
          END +
        CASE
           WHEN plz BETWEEN 65000 AND 65500 THEN 1
           ELSE 0
         END +
        CASE
               WHEN geschlecht = 2 THEN 1
               ELSE 0
        END DESC

Schon klar, der ORDER BY wiederholt die Berechnung des WHERE, was ich persönlich nicht optimal finde. Was ich eigentlich sagen wollte, war, dass das Zwischenergebnis des CASE nicht direkt als Wert materialisiert der dann im ORDER BY verwendet werden kann. In den beiden vorherigen Beispielen nutzt der ORDER BY das berechnete Zwischenergebnis.

Grüße
Thomas

[nikosch]

CASE ist zudem deutlich flexibler als IF, wenn es mal mehrere Kriterien in einem CASE - Block gibt.

Ja. Und IF ist deutlich prägnanter, wenn es um eine boolsche Bedingungs geht. SO what?

Für die Abfrage ist mir noch eine dritte Variante eingefallen.

Naja, die ist ja recht naheliegend. Und ORDER ist natürlich möglich:

SELECT * FROM members_daten
WHERE   CASE
           WHEN haarfarbe = 2 THEN 1
               ELSE 0
          END +
        CASE
           WHEN plz BETWEEN 65000 AND 65500 THEN 1
           ELSE 0
         END +
        CASE
               WHEN geschlecht = 2 THEN 1
               ELSE 0
        END  >= 2  
ORDER BY CASE
           WHEN haarfarbe = 2 THEN 1
               ELSE 0
          END +
        CASE
           WHEN plz BETWEEN 65000 AND 65500 THEN 1
           ELSE 0
         END +
        CASE
               WHEN geschlecht = 2 THEN 1
               ELSE 0
        END DESC 

Echt? Der Standard kennt nur CASE, kein IF?

Ja, habe es eben noch mal mit ORACLE und IBM DB2 ausprobiert. CASE geht, IF nicht. MySQL ist aus meiner Sicht ziemlich Propritär mit seinen Speziallösungen. Open Source muss nicht immer gleich Standard bedeuten.

CASE ist zudem deutlich flexibler als IF, wenn es mal mehrere Kriterien in einem CASE - Block gibt.

Für die Abfrage ist mir noch eine dritte Variante eingefallen. Nicht ganz optimal, da so kein ORDER BY möglich ist.

mysql> SELECT * FROM members_daten
    -> WHERE   CASE
    ->            WHEN haarfarbe = 2 THEN 1
    ->                ELSE 0
    ->           END +
    ->         CASE
    ->            WHEN plz BETWEEN 65000 AND 65500 THEN 1
    ->            ELSE 0
    ->          END +
    ->         CASE
    ->                WHEN geschlecht = 2 THEN 1
    ->                ELSE 0
    ->         END  >= 2  ;
+----+--------+-----------+-------+------------+
| id | name   | haarfarbe | plz   | geschlecht |
+----+--------+-----------+-------+------------+
|  2 | Name-2 |         2 | 66000 |          2 |
|  4 | Name-4 |         2 | 65000 |          1 |
+----+--------+-----------+-------+------------+
2 rows in set (0.02 sec)

mysql>

Grüße
Thomas

[nikosch]

Echt? Der Standard kennt nur CASE, kein IF?

Als komplettes Beispiel könnte es so aussehen:

CREATE TABLE members_daten (
 id INT NOT NULL,
 name VARCHAR(50) NOT NULL,
 haarfarbe INT,
 plz INT,
 geschlecht INT,
 
 PRIMARY KEY (id)
);

INSERT INTO members_daten VALUES
( 1, 'Name-1', 1, 65000, 1 ),
( 2, 'Name-2', 2, 66000, 2 ),
( 3, 'Name-3', 1, NULL, NULL ),
( 4, 'Name-4', 2, 65000, 1 );

-- 1. Lösung ähnlich der von @nikosch plus zusätzlich der Abfrage nach der Trefferquote >= 2

SELECT treffer.* FROM (SELECT * , (IF (haarfarbe = 2, 1, 0) +
                                   IF (plz BETWEEN 65000 AND 65500, 1, 0) +
                                   IF (geschlecht = 2, 1, 0)
                                   ) AS trefferquote
                         FROM members_daten ) treffer               
 WHERE treffer.trefferquote >= 2
ORDER BY treffer.trefferquote DESC;

+----+--------+-----------+-------+------------+--------------+
| id | name   | haarfarbe | plz   | geschlecht | trefferquote |
+----+--------+-----------+-------+------------+--------------+
|  2 | Name-2 |         2 | 66000 |          2 |            2 |
|  4 | Name-4 |         2 | 65000 |          1 |            2 |
+----+--------+-----------+-------+------------+--------------+
2 rows in set (0.00 sec)

mysql>

-- 2. Lösung ähnlich der von @nikosch plus zusätzlich der Abfrage nach der Trefferquote >= 2, das Ganze aber gemäß SQL-Standard 

SELECT treffer.* FROM (SELECT * , CASE 
                                    WHEN haarfarbe = 2 THEN 1
                                    ELSE 0
                                   END +
                                  CASE 
                                    WHEN plz BETWEEN 65000 AND 65500 THEN 1
                                    ELSE 0
                                   END +
                                  CASE 
                                    WHEN geschlecht = 2 THEN 1
                                    ELSE 0
                                  END                                   
                                   AS trefferquote
                         FROM members_daten ) treffer               
 WHERE treffer.trefferquote >= 2
ORDER BY treffer.trefferquote DESC;

+----+--------+-----------+-------+------------+--------------+
| id | name   | haarfarbe | plz   | geschlecht | trefferquote |
+----+--------+-----------+-------+------------+--------------+
|  4 | Name-4 |         2 | 65000 |          1 |            2 |
|  2 | Name-2 |         2 | 66000 |          2 |            2 |
+----+--------+-----------+-------+------------+--------------+
2 rows in set (0.00 sec)

mysql>

Grüße
Thomas

[nikosch]

Zukünftig bitte unter Datenbanken posten.

[MOD: verschoben]

[Destruction]

wo z.B. nur 2 von 3 Kriterien stimmen usw.!

Jo, hatte nur gedacht dass das gewollt war.
Naja egal. Ich danke dir auch für diesen Hinweis *gg*.
Solche Sachen kann man immer mal gebrauchen

cu
Grüße

[nikosch]

Naja, WHERE/HAVING schränkt eben die Auswahl ein und ORDER BY gibt die Sortierung an. Was genau gewünscht ist, ist problemabhängig.

[Destruction]

ORDER BY  IF(haarfarbe = 'blond' , 1 , 0) +
          IF(alter     > 35      , 1 , 0) +
          IF(plz LIKE '234%'     , 1 , 0)
          DESC

Edit.: Ah ok sorry verstehe, du ordnest im Prinzip dann nach der Gesamzahl.

Wäre es aber nicht einfacher dann einfach die ganzen zusammenzuaddiern und dann mit eine HAVING / WHERE - Abfrage zu schauen ob `anzahl` > 3 ist z.B. ?

Danke
cu
Grüße

[wolf29]

..ich auch nicht, deswegen -> super Hinweis!

Wolf29

[Asipak]

Cool, didn't know that!

[wolf29]

Ahh...vielen Dank. Guter Hinweis!

Wolf29