Join über drei Tabellen

[hamburger_1983]

Hallo liebe Forumgemeinde,

kommen wir nun gleich mal zu meinem nächsten Problem, an dem ich nun schon trotz Google und SuFu Stunden lang dran sitze:

Ich habe drei Tabellen

Tabelle 1: Kunden
Tabelle 2: CDs
Tabelle 3: DVDs

Alle drei Tabellen sind über die Spalte "kunden_id" zueinander referenziert.

CDs und DVDs sidn von der Struktur her exakt gleich aufgebaut, nur halt inhaltlich anders.

Die Abfrage an der ich mir nun die Zähne ausbeiße ist, wie ich herausbekomme, wieviele Datenträger (CDs und DVDs) welcher Kunde besitzt, die nach einem Vorkommen im Titel herausgesucht werden.

Die Suchbedingungen sind also

dvds.titel LIKE "%irgendwas%"
css.titel LIKE "%irgendwas%"
dvds.kunden_id = kunden.kunden_id
cds.kunden_id = kunden.kunden_id

Ich habe das ganze mit Union probiert, nur bekomme ich meinen Count dann nicht so hin wie ich will. Mit Join wird es evtl. auch gehen, nur sind die nicht ganz so mein Spezialgebiet.

[Falk Prüfer]

Hallo,

...Ich habe das ganze mit Union probiert, nur bekomme ich meinen Count dann nicht so hin wie ich will. Mit Join wird es evtl. auch gehen, nur sind die nicht ganz so mein Spezialgebiet.

Sollte sich aber relativ einfach mit Join lösen lassen. Notwendig sind hier OUTER JOINs, da es ja durchaus mgl. ist, dass ein Kunde zwar CDs aber keine DVDs bzw. umgekehrt besitzt. Wichtig ist dabei, dass die Auswahlbedingung "LIKE "%irgendwas%"" hier an die JOIN-Bedingung gehört und NICHT in die Where-Klausel.

[highlight=sql]
select k.name, count(distinct c.id) anz_cds, count(distinct d.id) anz_dvds
from kunden k
left join cds c on c.kunden_id = k.kunden_id and c.titel LIKE "%irgendwas%"
left join dvds d on d.kunden_id = k.kunden_id and d.titel LIKE "%irgendwas%"
group by k.name
[/highlight]

Wichtig! Das DISTINCT in der Count-Funktion, sonst werden hier die Scheiben doppelt und mehrfach gezählt.

Gruß Falk

[hamburger_1983]

Danke. das hat mir schonmal geholfen.

Nun komme ich an einer anderen Stelle ins Stocken:

SELECT c.customer_id, CONCAT(IF(c.gender = 'male', 'Mr.', 'Ms.'), " ", c.firstname, " ", c.lastname) AS customer_name, (count(DISTINCT s1.sale_id)+count(DISTINCT s2.sale_id)) AS sales_count, (SUM(s1.sale_amount)+SUM(s2.sale_amount)) AS sales_amount, MAX(s1.sale_date)
FROM customer c
LEFT JOIN sales1 s1 ON s1.customer_id = c.customer_id AND s1.sale_date >= '2007-01-01 00:00:00' AND s1.sale_date <= '2007-12-31 23:59:59'
LEFT JOIN sales2 s2 ON s2.customer_id = c.customer_id AND s2.sale_date >= '2007-01-01 00:00:00' AND s2.sale_date <= '2007-12-31 23:59:59'
GROUP BY c.firstname

Bei dem Code möchte ich so was ähnliches wie MAX(s1.sale_date und s2.sale_date). Also den Höchstwert der beiden Tabellenspalten.

[defo]

Das kommt auf Deinen SQL Server an. Unter Oracle geht:

SQL>  select greatest(1,2) from dual;
 
GREATEST(1,2)
-------------
            2
 
SQL>

Unter MS gabs das früher nicht.

[hamburger_1983]

Also, das mit Greatest scheint schon mal in die richtige Richtung zu gehen.

Eingebunden wie folgendermaßen:

SELECT c.customer_id, CONCAT(IF(c.gender = 'male', 'Mr.', 'Ms.'), " ", c.firstname, " ", c.lastname) AS customer_name, (count(DISTINCT s1.sale_id)+count(DISTINCT s2.sale_id)) AS sales_count, (SUM(s1.sale_amount)+SUM(s2.sale_amount)) AS sales_amount, GREATEST(s1.sale_date, s2.sale_date)
FROM customer c
LEFT JOIN sales1 s1 ON s1.customer_id = c.customer_id AND s1.sale_date >= '2007-01-01 00:00:00' AND s1.sale_date <= '2007-12-31 23:59:59'
LEFT JOIN sales2 s2 ON s2.customer_id = c.customer_id AND s2.sale_date >= '2007-01-01 00:00:00' AND s2.sale_date <= '2007-12-31 23:59:59'
GROUP BY c.firstname

Allerdings scheint er nicht den greatest value zu nehmen.

wenn ich anstatt GREATEST(s1.sale_date, s2.sale_date) => MAX(s1.sale_date) einsetze erhalte ich für einen Wert z.B. '2007-04-02 11:37:06' - bei MAX(s1.sale_date) ist es '2007-05-06 11:38:14'. GREATEST(s1.sale_date, s2.sale_date) gibt mir aber '2007-04-12 11:37:18' aus.

[fanderlf]

Auf welcher Datenbank bist Du denn unterwegs? Ist bei Dir die Spalte sale_date als DateTime Datentyp deklariert? Unterstützt die Funktion Greatest DateTime Daten? Das wären die Sachen die ich jetzt mal checken würde

[defo]

Ich möchte noch anfügen:
Du vergleichst da das Ergebnis einer Aggregatfunktion mit einer normalen Funktion, ohne dass im restlichen Select (das sowieso seltsam aussieht) zu berücksichtigen.
Du verwendest offenbar Strings als Datum, welchen Typ die Date Felder wirklich haben, ist (zumindest mir) unklar.

[Falk Prüfer]

Hallo,

nach der Gruppierung zu urteilen wird es wohl MySQL sein. Jede andere DB würde diese Abfrage NICHT ausführen.
Deine Gruppierung ist schlichtweg falsch! Du listest c.customer_id, c.gender, c.firstname und c.lastname, gruppierst jedoch nur nach c.firstname. Ich glaube nicht dass c.firstname eindeutig und die anderen Felder davon funktional abhängig sind. Bei dieser Gruppierung wirst du also IMMER nicht nachvollziehbare Ergebnisse erhalten.

Gruß Falk