zapytanie mysql

Hej

Mam takie zapytanie mysql.
Jak zmusic je do wyswietlania TYLKO tych kategorii, które zwracają noi > 0
ORAZ subcategories > 0 ?

Probowalem dodawac warunki do WHERE ale bez skutku.

Z gory dzieki za pomoc.

SELECT
(SELECT COUNT(items.id) FROM items WHERE items.id_category = categories.id
AND items.active = 1) AS noi,
categories.name, categories.id AS id_category,
(SELECT COUNT(categories.id) FROM categories WHERE categories.active = 1 AND
categories.id_parent = 0) AS total,
(SELECT COUNT(categories.id) FROM categories WHERE categories.id_parent =
id_category AND categories.active = 1) AS subcategories
FROM
categories
WHERE categories.id_parent = 0 AND categories.active = 1
ORDER BY noi DESC, categories.name ASC

Pozdr
b

"Byru" <byru@gazeta.pl> wrote in message
news:feq55r$pe9$1@inews.gazeta.pl...
> Mam takie zapytanie mysql.
> Jak zmusic je do wyswietlania TYLKO tych kategorii, które zwracają noi > 0
> ORAZ subcategories > 0 ?
>
> Probowalem dodawac warunki do WHERE ale bez skutku.
>
> Z gory dzieki za pomoc.
>
> SELECT
> (SELECT COUNT(items.id) FROM items WHERE items.id_category = categories.id
> AND items.active = 1) AS noi,
> categories.name, categories.id AS id_category,
> (SELECT COUNT(categories.id) FROM categories WHERE categories.active = 1
> AND categories.id_parent = 0) AS total,
> (SELECT COUNT(categories.id) FROM categories WHERE categories.id_parent =
> id_category AND categories.active = 1) AS subcategories
> FROM
> categories
> WHERE categories.id_parent = 0 AND categories.active = 1
> ORDER BY noi DESC, categories.name ASC

Bo w Where powinieneś dodać warunek typu:

(SELECT COUNT(items.id) FROM items WHERE items.id_category = categories.id
AND items.active = 1) > 0

Ja jednak zamiast wyliczać liczbę rekordów w podzapytaniach skorelowanych
robiłbym to w podzapytaniach użytych w klauzuli From, np:

Select
C.name, C.id As id_category,
(Select Count(*) From categories
Where id_parent = 0 AND active = 1) As Total,
S.Subcategories, I.Noi
From
categories As C
Inner Join
(Select id_parent, Count(*) As Subcategories
From categories Where active = 1 Group By id_parent) As S
On
S.id_parent = C.id
Inner Join
(Select id_category, Count(*) As Noi
From items Where active = 1 Group By id_category) As I
On
I.id_category = C.id

Ewentualnie można byłoby dla czytelności oba podzapytania źródłowe przenieść
do osobnych widoków.
Dzięki temu warunek ograniczający byłby bardzo prosty:

Where
I.Noi > 0 And S.Subcategories > 0

Przypuszczam, że takie przenosiny byłyby też z korzyścią dla wydajności.
A sortowanie sam dodasz
--
Pozdrawiam
Grzegorz
http://gdanowski.blogspot.com/