mysql - count / left join Problem

dieter99 · 09.02.2007, 17:38

Hallo,
ein einfaches Beispiel:

Tabelle1
id_a - - - produkt - - - - - - datum
1 - - - - - - - Audi- - - - - - - - 4534532523
2 - - - - - - - Mercedes - - - - - 4534532523
3 - - - - - - - Audi - - - - - - - - 4534532523
4 - - - - - - - Porsche - - - - - - 4534532523
5 - - - - - - - VW - - - - - - - - -4534532523
6 - - - - - - - Seat - - - - - - - - 4534532523
7 - - - - - - - Audi - - - - - - - - - 4534532523
8 - - - - - - - VW - - - - - - - - -4534532523

Tabelle2
id_b - - - produkt - - - - - - - spalte x
1 - - - - - - - Renault - - - - - - dsfasdfasdf
2 - - - - - - - Ferrari - - - - - - - dsfasdf
3 - - - - - - - Audi - - - - - - - - dsfasdf
4 - - - - - - - Opel - - - - - - - - adfasdfas
5 - - - - - - - Audi - - - - - - - - dsfsadfasdf
6 - - - - - - - VW - - - - - - - - - asdf

Zunächst möchte ich aus Tabelle 1 die Automarken ausgeben lassen, sowie die Anzahl. Beispiel:

Audi 3
Mercedes 1
Porsche 1
VW 2
Seat 1

Das mySQL Statement lautet ungefähr so:

PHP-Code:


			
SELECT produkt as name, count(produkt) as anzahl FROM tabelle1 GROUP BY produkt

Bei der Ausgabe muss ich jedoch nun auch überprüfen, ob dieses Fabrikat in Tabelle2 mindestens 1mal auftaucht. Das Ergebnis soll dann ungefähr so aussehen:

Audi 3 [JA]
Mercedes 1 [NEIN]
Porsche 1 [NEIN]
VW 2 [JA]
Seat 1 [NEIN]

[JA] bedeutet, zu diesen Fabrikat gibt es in Tabelle 2 mindestens 1 Eintrag.
[NEIN] bedeutet das es zu diesen Fabrikat in Tabelle 2 keinen Eintrag gibt

Ich dachte an folgendes SQL-Statement:

PHP-Code:


			
SELECT tabelle1.produkt as name, count(tabelle1.produkt) as anzahl, tabelle2.id_b FROM tabelle1 LEFT JOIN tabelle2 ON tabelle2.produkt=tabelle1.produkt GROUP BY produkt

Ich kann jetzt zwar prüfen ob es in der Tabelle 2 zu diesen Fabrikat auch einen Eintrag gibt (über die tabelle2.id_b), jedoch wird jetzt die Anzahl der Fabrikate aus Tabelle 1 falsch berrechnet (da in der Tabelle 2 die Fabrikate zum Teil mehrfach vorhanden ist).

Wie kann ich das Problem lösen so dass trotzdem die Anzahl aus Tabelle 1 richtig berechnet wird?

Ich hoffe ich habe mich einigermaßen verständlich ausgedrückt.

Danke für die Unterstützung
Gruss
Dieter

Rizzo · 10.02.2007, 07:46

Versuch mal ob das hier geht...

PHP-Code:


			
$sql = "SELECT

            h.id as hid, h.fabrikat as fab, COUNT(h.fabrikat) as anzahl,

            (SELECT IF(COUNT(marke) > 1, 'Ja', 'Nein') FROM automarke WHERE marke = h.fabrikat) as menge

        FROM

            hersteller h

        LEFT JOIN

            automarke a

        ON

            h.id = a.id

        GROUP BY

            h.fabrikat

        ORDER BY

            h.id

        ";

Setzt MySQL >= 4.1 voraus. Tabellennamen und Felder musst Du dir natürlich anpassen.

dieter99 · 12.02.2007, 08:18

@ Rizzo: der Code hat zwar nicht ganz gepasst, aber du hast mich zur Lösung verholfen. VIELEN DANK!

Dieser Code muss raus

PHP-Code:


			
LEFT JOIN automarke a

denn sonst passiert der gleiche Zählfehler wie bei meinem Code.

Außerdem muss es statt

PHP-Code:


			
SELECT IF(COUNT(marke) > 1

so heißen:

PHP-Code:


			
SELECT IF(COUNT(marke) >= 1

Sonst würde die Abfrage nur zutreffen wenn die Marke mindestens 2 Mal vorkommt.

Wo kann man eigentlich diese Verschachtelung von SELECT Anweisungen nachlesen????

Rizzo · 12.02.2007, 11:58

Hallo

Sorry, war ein Flüchtigkeitsfehler mit der COUNT Abfrage.

Das der LEFT JOIN raus muß liegt dann wohl an deiner konkreten Datenbank-Struktur. Ich hab das hier lokal mit Dummy-Tabellen und LEFT JOIN getestet und da funktionierte es tadellos.

Zitat:

Wo kann man eigentlich diese Verschachtelung von SELECT Anweisungen nachlesen????

Einfach mal googlen nach MySQL Subselect dann findet man schon eine ganze Menge Info. Das wichtigste das man verstehen muß ist, daß Queries mit Subselects immer von innen nach aussen abgearbeitet werden.

Hier ein Beispiel wie es bei einem News-Script aussehen könnte das mit einer separaten Kommentar-Tabelle arbeitet. Wenn man die News herausfinden möchte zu der am meisten Kommentare existieren, könnte die Abfrage so aussehen... (aus dem Kopf ohne zu testen, deswegen ohne Gewähr)

Code:

$sql = "SELECT * FROM news WHERE id = (SELECT newsid FROM kommentare GROUP BY newsid ORDER BY COUNT(id) DESC LIMIT 1)
		LIMIT 1";

In dem Beispiel wird erst der Subquery (rote Schrift) abgearbeitet, der die newsid ermittelt für die am meisten Kommentare vorhanden sind. Dann wird die newsid an die WHERE Klausel der Hauptabfrage übergeben und die News mit der entsprechenden id wird ausgelesen.

Man könnte das noch weiter verschachteln, z.B. dahingehend das man die News finden möchte zu der am meisten Kommentare existieren, wobei ein Kommentar von einem bestimmten User vorhanden sein muß.

Code:

$sql = "SELECT * FROM news WHERE id = (SELECT newsid FROM kommentare WHERE username LIKE (SELECT name FROM user WHERE id = '17') GROUP BY newsid ORDER BY COUNT(id) DESC LIMIT 1)
		LIMIT 1";

Hier wird erst die grüne Abfrage abgearbeitet und anhand der ID wird der Benutzername ermittelt. Dieser wird an die rote Abfrage übergeben.... usw.

Prinzipiell sind Subqueries nicht schwer zu verstehen und erleichtern einem das Leben im täglichen Kampf mit MySQL.

dieter99 · 13.02.2007, 10:53

Ich hab jetzt mal ein bischen gegoogelt und bin dabei auf folgende Seite gestoßen:

http://www-user.tu-chemnitz.de/~dgr/...nf/teil17.html

Dort steht das eine SQL Abfrage mit Subselect 10 Stunden gedauert hätte, mit LEFT JOIN jedoch nur 20 Sekunden (siehe grüner Text).

Erzeugen die Subselects tatsächlich eine hohe Auslastung?