group by und count über mehrere tabellen

hi zusammen,
ich zerbreche mir gerade den kopf wie ich folgendes problem löse und bin fast am verzweifeln.
ich habe drei tabellen

tabelle:filme
tabelle:Texte (verweiß auf filme mit fid)
tabelle:links (verweiß auf filme mit fid)

zu jedem film können mehrere texte angelegt sein unabhängig davon zu jedem film xbeliebig viele links
in filme liegen drei filme drin zu jedem film existieren 3 links und jeweils zu jedem film ein Text

ich möchte wissen wie viele texte und links pro film existieren

ergebnis sollte so aussehen
film , clinks, ctexte
film1, 3, 1
film2, 3, 1
film3, 3, 1

mein bisheriger ansatz:
SELECT
f.*,
count(l.Fid) as clinks,
count(tx.Fid)as ctexte
FROM
film as f
LEFT JOIN (text as tx, link as l)
ON (f.Fid = tx.Fid and f.Fid = l.Fid)
GROUP BY
f.Fid

jetzt bin ich mir gar nicht mehr sicher ob ich das überhaupt mit group by lösen kann oder ob ich subselects nehmen muss
ich sage noch dazu dass ich mysql her nehme


danke schon mal im voraus
lg timo

Was schon mal nicht geht: select f.* und weiter hinten GROUP BY f.id. Jede richtige Datenbank wird Dir an dieser Stelle einen Fehler an den Kopf werfen, weil eben die Gruppierung für die Zählerei nicht korrekt definiert ist. MySQL ignoriert soweit ich weiß Deinen Fehler und würfelt irgendwas aus dem Entropiepool raus.

test=*# select * from filme ;
 fid | film
-----+-------
   1 | film1
   2 | film2
   3 | film3
(3 rows)

test=*# select * from texte ;
 fid |    t
-----+----------
   1 | text_1_1
   1 | text_1_2
   1 | text_1_3
   2 | text2_1
(4 rows)

test=*# select * from links ;
 fid |    l
-----+----------
   2 | link_2_1
   2 | link_2_2
   2 | link_2_3
   3 | link_3_1
(4 rows)
test=*# select f.fid, f.film, t.t, l.l  from filme f left join (select fid, count(1) as t from texte group by fid) t on (f.fid=t.fid) left join (select fid, count(1) as l from links group by fid) l on (f.fid=l.fid) order by fid;
 fid | film  | t | l
-----+-------+---+---
   1 | film1 | 3 |
   2 | film2 | 1 | 3
   3 | film3 |   | 1
(3 rows)

Hint: wirf dein MySQL weg und nehme PostgreSQL, denn das hier ist ein Forum, wo es um PostgreSQL geht. Du wirst hier ansonsten recht schnell zwar Lösungen für Deine Probleme bekommen, die aber NICHT mit MySQL funktionieren werden.

Ach ja: und formatiere Code passend.

Andreas

Geht doch recht einfach:

SELECT f.*,
       count(l.Fid) over (partition by f.id) f.as clinks,
       count(tx.Fid) over (partition by f.id) as ctexte
FROM film as f
  LEFT JOIN texte as tx ON tx.fid = f.fid
  LEFT JOIN links as l ON f.Fid = l.Fid

(Ach ja: funktioniert nur mit modernen Datenbanken also auch mit der DB um die es hier geht)

Also bei meiner mysql Datenbank funktioniert das query nicht

habe aber eine andere abfrage zusammen gebastelt. mit subselects, damit war es denkbar einfach

SELECT f . * , (

SELECT count( t.Fid )
FROM text t
WHERE t.Fid = f.Fid
) AS ctext, 
(
SELECT count( l.Fid )
FROM link l
WHERE l.Fid = f.Fid
) AS clink

FROM film f

Natürlich nicht. Ich sagte ja “moderne” Datenbank.
Nachdem Du Deine Frage in einem PostgreSQL Forum gestellt hast, dachte ich Du willst ein Abfrage für PostgreSQL.

oh sorry, dass habe ich nicht gesehen, dass das ein Post im PostgreSQL ist :wink:

Ist klar, hast das ja mit der Gieskanne in mehrere Foren gekippt …

Andreas

Steht ja nur ganz dick und fett oben links im Browser.