LEFT JOIN + COUNT + 0 Opcje

[phpion]

Hej!
Mam 2 tabele

[SQL] pobierz, plaintext 
CREATE TABLE download (
	id SMALLINT(4) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	category TINYINT(2) UNSIGNED NOT NULL DEFAULT '1',
	name VARCHAR(50) NOT NULL,
	size FLOAT(7,1) NOT NULL,
	visible ENUM('0', '1') NOT NULL DEFAULT '1',
	date DATE NOT NULL
);
 
CREATE TABLE download_categories (
	id TINYINT(3) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(50) NOT NULL,
	visible ENUM('0', '1') NOT NULL DEFAULT '1'
);
[SQL] pobierz, plaintext 

Oraz zapytanie:

[SQL] pobierz, plaintext 
SELECT dc.id, dc.name, COUNT(d.id) AS counter FROM download_categories AS dc LEFT JOIN download AS d ON (dc.id=d.category) WHERE d.visible='1' GROUP BY d.category ORDER BY d.name DESC
[SQL] pobierz, plaintext 

ktore ma za zadanie wyswietlic dane:
id kategorii | nazwa kategorii | liczba plikow
gdzie zliczanie liczby plikow powinno zliczac tylko te, ktorych visible='1'. No i dziala fajnie, ale jesli w danej kategorii nie ma zadnego pliku to nie uwzglednia jej w zestawieniu (IMG:http://forum.php.pl/style_emoticons/default/sad.gif) Jak poprawic to zapytanie aby wyswietlilo takze puste kategorie (z counter rownym 0).
PS: nigdy JOINow nie czailem wiec jesli widac jakies razace bledy prosze o info (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg)

[siemakuba]

Tak na szybko to wydaje mi się, że powinieneś zmienić grupowanie.
Zamiast

[SQL] pobierz, plaintext 
GROUP BY d.category
[SQL] pobierz, plaintext 

spróbuj dać

[SQL] pobierz, plaintext 
GROUP BY dc.id
[SQL] pobierz, plaintext 

pozdr.

[phpion]

NIestety dziala tak samo... Ponizej zamieszczam poszczegolne zapytania i efekty ich wywolan:

[SQL] pobierz, plaintext 
mysql> SELECT dc.id, dc.name, COUNT(d.id) AS counter FROM download_categories AS dc LEFT JOIN download AS d ON (dc.id=d.category) WHERE d.visible='1' GROUP BY d.category ORDER BY d.name DESC;
+----+------------------+---------+
| id | name             | counter |
+----+------------------+---------+
| 12 | Mp3              |       2 |
| 11 | Dokumentacja SAP |       5 |
+----+------------------+---------+
2 rows IN SET (0.07 sec)
 
mysql> SELECT dc.id, dc.name, COUNT(d.id) AS counter FROM download_categories AS dc LEFT JOIN download AS d ON (dc.id=d.category) WHERE d.visible='1' GROUP BY dc.id ORDER BY d.name DESC;
+----+------------------+---------+
| id | name             | counter |
+----+------------------+---------+
| 12 | Mp3              |       2 |
| 11 | Dokumentacja SAP |       5 |
+----+------------------+---------+
2 rows IN SET (0.00 sec)
 
mysql> SELECT * FROM download; +----+----------+-------------------------------------+--------+---------+------------+
| id | category | name                                | size   | visible | date       |
+----+----------+-------------------------------------+--------+---------+------------+
|  1 |        2 | autoexec.cfg                        |    0.2 | 1       | 2006-11-25 |
|  2 |        2 | readme.html                         |    6.2 | 1       | 2006-11-25 |
|  3 |        2 | cube_unix                           |    0.8 | 1       | 2006-11-25 |
|  4 |        2 | 10533-rs.jpg                        |   35.9 | 0       | 2006-11-25 |
|  5 |        2 | 4326-b.jpg                          |   69.4 | 1       | 2006-11-25 |
|  6 |        2 | 9628-rs.jpg                         |   39.5 | 1       | 2006-11-25 |
|  7 |        3 | 9823-rs.jpg                         |   46.5 | 1       | 2006-11-25 |
|  8 |        2 | 10464-rs.jpg                        |    7.7 | 1       | 2006-11-25 |
|  9 |        1 | 10890-rs.jpg                        |   18.0 | 1       | 2006-11-25 |
| 10 |        1 | 10890-b.jpg                         |   42.7 | 1       | 2006-11-25 |
| 11 |        1 | program.doc                         |   34.5 | 1       | 2006-11-25 |
| 12 |        1 | MOJEbazy26042006.txt                |    2.3 | 1       | 2006-11-25 |
| 13 |        2 | 00a Wprowadzenie 2004 C PL.pdf      | 1625.1 | 1       | 2006-11-26 |
| 14 |        4 | 01 Administracja 2004 C PL.pdf      | 1719.4 | 1       | 2006-11-26 |
| 21 |       11 | 01 Administracja 2004 C PL.pdf      | 1719.4 | 1       | 2006-11-26 |
| 25 |       11 | 04 Sprzedaz 2004 C PL.pdf           |  922.1 | 1       | 2006-11-26 |
| 26 |       11 | 05 Zakup 2004 C PL.pdf              | 1549.2 | 1       | 2006-11-26 |
| 19 |       11 | 00a Wprowadzenie 2004 C PL.pdf      | 1625.1 | 1       | 2006-11-26 |
| 28 |       12 | klubowy.MP3                         | 1606.0 | 1       | 2006-11-26 |
| 27 |       11 | 06 Partnerzy handlowi 2004 C PL.pdf |  842.1 | 1       | 2006-11-26 |
| 30 |       12 | rottweiler_new.MP3                  | 1562.1 | 1       | 2006-11-26 |
+----+----------+-------------------------------------+--------+---------+------------+
21 rows IN SET (0.00 sec)
 
mysql> SELECT * FROM download_categories; +----+------------------+---------+
| id | name             | visible |
+----+------------------+---------+
| 13 | Zdjcia          | 1       |
| 12 | Mp3              | 1       |
| 11 | Dokumentacja SAP | 1       |
+----+------------------+---------+
3 rows IN SET (0.00 sec)
[SQL] pobierz, plaintext 

Ten post edytował phpion.com 26.11.2006, 13:53:18

[bumelang]

Spróbuj coś w tym stylu

[SQL] pobierz, plaintext 
SELECT id, name, (SELECT COUNT(*)
                    FROM download WHERE category = dc.id AND visible = true)
  FROM download_categories AS dc
[SQL] pobierz, plaintext 

Oczywiście nie wiem, czy to zadziała z palca, ale chyba rozumiesz ideę.

[phpion]

Dziala! Dzieki.