@Omata: Vielen Dank, die Abfrage sieht vielversprechend leichter aus. Leider kommt der Fehler, dass bei "WHERE s.alevel <= 1" die Spalte s.alevel nicht bekannt sei. Ich werde morgen versuchen, die Abfrage entsprechend anzupassen! Danke!
Anbei habe ich als Anhang meine kompletten Tabellendefinitionen mit Daten (= original-Daten meiner Test-Datenbank, so wie ich sie benutze) beigefügt!!!
Hier meine bisherige Abfrage, die - wohl? - funktioniert. Die 3. Abfrage listet alle Bilder auf, welche KEINE Unterkategorie haben, sondern gleich einer Hauptkategorie
zugeordnet sind (In diesen Hauptkategorien [denen Bilder direkt zugeordnet sind] dürfen übrigens KEINE Unterkategorien vorhanden sein).
SQL-Code:
SELECT catid, catname, alevel, lastupdate, klicks, alname, max(subcats) as subcats, max(pictures) as pictures
FROM
(
SELECT c.id As catid, c.itemname As catname, a.alevel As alevel, c.lastupdate As lastupdate, c.klicks As klicks, a.itemname As alname, 0 as subcats, count(p.id) As pictures
FROM tblcats c
LEFT JOIN tblsubcats s
ON c.id = s.parentcat
LEFT JOIN tblpics p
ON p.parentsubcat = s.id
INNER JOIN tblaccesslevels a
ON c.alevel = a.alevel
WHERE s.alevel <= 0
GROUP BY c.itemname
HAVING alevel <= 0
UNION
SELECT c.id As catid, c.itemname As catname, a.alevel As alevel, c.lastupdate As lastupdate, c.klicks As klicks, a.itemname As alname, count(s.id) as subcats, 0 As pictures
FROM tblcats c
LEFT JOIN tblsubcats s
ON c.id = s.parentcat
INNER JOIN tblaccesslevels a
ON c.alevel = a.alevel
WHERE s.alevel <= 0
GROUP BY c.itemname
HAVING alevel <= 0
UNION
SELECT c.id As catid, c.itemname As catname, a.alevel As alevel, c.lastupdate As lastupdate, c.klicks As klicks, a.itemname As alname, count(s.id) as subcats, count(p.id) As pictures
FROM tblcats c
LEFT JOIN tblsubcats s
ON c.id = s.parentcat
LEFT JOIN tblpics p
ON c.id = p.parentcat
INNER JOIN tblaccesslevels a
ON c.alevel = a.alevel
WHERE c.alevel <= 0
GROUP BY c.itemname
HAVING subcats = 0
) sq
GROUP BY catname
Danke nochmals an alle Helfer
SCRaT