[MySql] Aggiungere clausola a Join

[|\/|atrix]

Salve a tutti,
sto cercando di approfondire i Nested Sets e le Funzioni Aggregate e mi ritrovo davanti ad un problema stupido che però non riesco a risolvere...

Ho scritto questa semplice query che estrapola dal database tutte le categorie che contengono direttamente o indirettamente (per indirettamente mi riferisco ai prodotti contenuti nelle sottocategorie della categoria, che a rigor di logica sono contenuti anche alla categoria madre relativa) almeno un prodotto mostrando il numero totale di prodotti contenuti in ogni ramo.

Questa è la query che è perfettamente funzionante:

SELECT 
parent.id, 
parent.nome,
COUNT(prodotti.id) AS numProdContenuti

FROM TABELLA_CATEGORIE AS node

INNER JOIN TABELLA_CATEGORIE AS parent ON node.lft BETWEEN parent.lft AND parent.rgt
INNER JOIN TABELLA_PRODOTTI AS prodotti ON node.id = prodotti.category_id

GROUP BY parent.id
ORDER BY node.lft 

Ora, il mio problema è che vorrei che la query mi mostri (e quindi ricalcoli il numero dei prodotti in base a questo nuovo criterio) solo le categorie dove il campo "visible" sia 1.
Le ho provate tutte, riuscendo a non mostrare le categorie con "visible" = 0 ma i prodotti contenuti nelle categorie nascoste mi vengono conteggiati lo stesso (e non dovrebbe essere così).

Mi date un aiutino?
Grazie

P.s. Per una maggiore facilità vi posto la struttura delle due tabelle:

TABELLA CATEGORIE
+-------------+----------------------+-----+-----+---------+
|          id | nome                 | lft | rgt | visible |
+-------------+----------------------+-----+-----+---------+
|           1 | ELECTRONICS          |   1 |  20 |    1    |
|           2 | TELEVISIONS          |   2 |   9 |    1    |
|           3 | TUBE                 |   3 |   4 |    1    |
|           4 | LCD                  |   5 |   6 |    1    |
|           5 | PLASMA               |   7 |   8 |    0    |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |    1    |
|           7 | MP3 PLAYERS          |  11 |  14 |    1    |
|           8 | FLASH                |  12 |  13 |    1    |
|           9 | CD PLAYERS           |  15 |  16 |    1    |
|          10 | 2 WAY RADIOS         |  17 |  18 |    1    |
+-------------+----------------------+-----+-----+---------+

TABELLA PRODOTTI
+------------+-------------------+-------------+
|         id | name              | category_id |
+------------+-------------------+-------------+
|          1 | 20  TV            |           3 |
|          2 | 36  TV            |           3 |
|          3 | Super-LCD 42      |           4 |
|          4 | Ultra-Plasma 62   |           5 |
|          5 | Value Plasma 38   |           5 |
|          6 | Power-MP3 128mb   |           7 |
|          7 | Super-Shuffle 1gb |           8 |
|          8 | Porta CD          |           9 |
|          9 | CD To go          |           9 |
|         10 | Family Talk 360   |          10 |
+------------+-------------------+-------------+ 

[|\/|atrix]

La cosa stranissima è che ho provato con questa query:

SELECT 
parent.id, 
parent.nome, 
COUNT(prodotti.id) AS numProdContenuti 

FROM TABELLA_CATEGORIE AS node 

INNER JOIN TABELLA_CATEGORIE AS parent ON node.lft BETWEEN parent.lft AND parent.rgt 
INNER JOIN TABELLA_PRODOTTI AS prodotti ON node.id = prodotti.category_id AND node.visible = 1

GROUP BY parent.id 
ORDER BY parent.lft 

che funziona per come dovrebbe con l'esempio di struttura di tabella che ho postato prima, ma non funziona con quella che ho io!!!
E cioè:

TABELLA CATEGORIE
+-------------+--------------------------+-----+-----+---------+
|          id | nome                     | lft | rgt | visible |
+-------------+--------------------------+-----+-----+---------+
|           1 | ROOT CAT                 |   1 |  12 |    1    |
|           2 | SUB CAT 1 (in root cat)  |   2 |  11 |    1    |
|           3 | SUB CAT 2 (in sub cat 1) |   7 |  10 |    0    |
|           4 | SUB CAT 3 (in sub cat 2) |   8 |   9 |    1    |
|           5 | SUB CAT 4 (in sub cat 1) |   3 |   6 |    1    |
|           6 | SUB CAT 5 (in sub cat 4) |   4 |   5 |    1    |
+-------------+--------------------------+-----+-----+---------+

TABELLA PRODOTTI
+------------+----------------------+-------------+
|         id | name                 | category_id |
+------------+----------------------+-------------+
|          1 | OGGETTO IN SUB CAT 5 |       6     |
|          2 | OGGETTO IN SUB CAT 3 |       4     |
+------------+----------------------+-------------+ 

Il risultato che dovrebbe essere:

--- 1 ROOT CAT - 1 oggetto
--------- 2 SUB CAT 1 (in root cat) - 1 oggetto 
--------------- 5 SUB CAT 4 (in sub cat 1) - 1 oggetto 
----------------------- 6 SUB CAT 5 (in sub cat 4) - 1 oggetto 

è invece:

--- 1 ROOT CAT - 2 oggetti
--------- 2 SUB CAT 1 (in root cat) - 2 oggetti 
--------------- 5 SUB CAT 4 (in sub cat 1) - 1 oggetto 
----------------------- 6 SUB CAT 5 (in sub cat 4) - 1 oggetto 
--------------- 3 SUB CAT 2 (in sub cat 1) - 1 oggetto 
----------------------- 4 SUB CAT 3 (in sub cat 2) - 1 oggetto 

... non ci sto a capì più nulla!

[|\/|atrix]

Ho risolto da solo con questa query:

SELECT
parent.id,
parent.nome,
COUNT(prodotti.id) AS numProdContenuti

FROM TABELLA_CATEGORIE AS node

INNER JOIN TABELLA_CATEGORIE AS parent
ON node.lft BETWEEN parent.lft AND parent.rgt
AND (SELECT id FROM TABELLA_CATEGORIE WHERE lft <= node.lft AND rgt >= node.rgt AND visible = 0 LIMIT 0,1) IS NULL

INNER JOIN TABELLA_PRODOTTI AS prodotti
ON node.id = prodotti.category_id

GROUP BY parent.id
ORDER BY parent.lft 

Il problema è che vorrei "snellirla" un po, espletando casomai con una join la subquery... mi sembra un po troppo pesante (forse è la subquery) mi date una mano?

Grazie

[|\/|atrix]

up

[Razorblade]

Ciao, dopo tutti questi post non ho ben capito a che punto tu sia del problema, io ti posto la query come la farei io, tenendo conto che nelle join uso come convenzione chiamare le tabelle come a, b, c ... e così via e i nomi dei campi CamelCase per maggiore comprensibilità:

SELECT a.id AS idCategoria, a.nome AS nomeCategoria, b.id AS idProdotto, b.nome AS nomeProdotto FROM ( TABELLA_CATEGORIE a LEFT JOIN TABELLA_PRODOTTI b ON a.id = b.category_id ) WHERE a.visible = 1 

Non ho ben capito l'uso dei campi 'lft' e 'rgt' ma immagino il tuo obiettivo fosse quello di organizzare le categorie su più livelli, per questo scopo personalmente uso le tabelle ricorsive, dove ogni id ha un id parent e un id radice.
Spero di essere stato utile.
Ciao

[|\/|atrix]

Ti ringrazio per la risposta...
putroppo con la tua query non risolvo il problema...
I campi lft e rgt sono relativi al nested set, il mio problema è che vorrei rendere meno pesante la query che ho già (funzionante) ma che richiede troppo tempo nell'esecuzione, aggiungendo una ulteriore clausola alla join, senza la subquery (SELECT id FROM TABELLA_CATEGORIE WHERE lft <= node.lft AND rgt >= node.rgt AND visible = 0 LIMIT 0,1)