404 messaggi dal 09 maggio 2012
Ciao ragazzi,

ho 2 tabelle (auto, dettagli_auto) cosi strutturate

AUTO (sono auto differenti)

id_a, marca

1, BMW
2, AUDI
3, AUDI
4, MERCEDES
5, FIAT
.....

DETTAGLI AUTO (sono tutti gli accessori che ogni auto ha)

id_d, id_a, id_accessorio, nome_accessorio

1,1,32,airbag
2,1,44,fari xenon
3,2,43,abs
4,2,32,airbag
5,3,44,fari xenon
6,3,43,abs
7,4,32,airbag
8,5,44,fari xenon
9,5,43,abs
..........

Ora voglio sapere:

1) quante auto ho in totale
2) quante auto ho con un determinato id_accessorio

Per ora ho utilizzato una select con inner join ma mi restituisce tutti i record ovvero mi risulta che ho 9 auto e non 5. Ovviamente nel caso reale ci sono moltissimi campi prelevati da entrambe le tabelle. è per un filtro. la query di base ha solo una WHERE sulla tabella auto:

select a.id_a, a.marca from auto as a INNER JOIN Auto_dettagli as b ON a.ID_a=b.id_a where a.visibilita=1 AND a.stato <> 0 order by a.id_a DESC

questa query mi restituisce 9 auto e non 5
1.976 messaggi dal 27 luglio 2005
Contributi
salve,
la clausola di JOIN mette in relazione le 2 tabelle e produce il relativo prodotto cartesiano: avendo 9 righe in Dettagli_Auto (tutte correttamente relazionanti un [Auto] esistente [tra l'altro , 3 e 4 sono entrambi AUDI :D:D ], il risultato sara' ovviamente di proiettare tutte le righe di Dettagli_Auto con indicazione della relazione con Auto.
quindi
SELECT *
  FROM dbo.[Auto] a
    JOIN dbo.Dettagli_Auto d ON d.id_a = a.id_a;

restituisce 9 righe di accessori correlati alle rispettive auto, ma NON 9 auto... concettualmente e' molto diverso :D

quindi, per sapere quante auto "hai" che siano relazionate con altri accessori, il filtro basico diventa
SELECT a.[id_a], a.[marca]
  FROM dbo.[Auto] a
    JOIN dbo.Dettagli_Auto d ON d.id_a = a.id_a
  GROUP BY a.[id_a], a.[marca];
--<-----------
id_a        marca
----------- ----------
1           BMW
2           AUDI
3           AUDI
4           MERCEDES
5           FIAT    


che correttamente restituisce le 5 righe di Auto che abbiano almeno una relazione in Dettaglio_Auto

volendo poi avere l'elenco delle auto con un determinato accessorio, ad esempio 'fari xenon', devi aggiungere ai tuoi ulteriori filtri (qui non disponibili/validabili) il filtro
WHERE d.id_accessorio = 44
quindi
SELECT *
  FROM dbo.[Auto] a
    JOIN dbo.Dettagli_Auto d ON d.id_a = a.id_a
  WHERE d.id_accessorio = 44
--<----------
id_a        marca      id_d        id_a        id_accessorio nome_accessorio
----------- ---------- ----------- ----------- ------------- ---------------
1           BMW        2           1           44            fari xenon
3           AUDI       5           3           44            fari xenon
5           FIAT       8           5           44            fari xenon


non sono concettualmente molto d'accordo sulla modellazione della tabella Dettagli_Auto,
per me id_d int come chiave primaria NON e' necessaria ed avrei modellato la chiave primaria composta da id_a + id_accessorio
tanto piu' che la modellazione corretta in 3za forma normale sarebbe stata un'altra tabella ancora, [Dettagli], con la tabella di correlazione molti_a_molti [Dettagli_Auto], quindi con una modellazione
CREATE TABLE dbo.[Auto] (
  id_a int NOT NULL PRIMARY KEY,
  marca varchar(10)
  );
CREATE TABLE dbo.Dettagli (
  id_d int NOT NULL PRIMARY KEY,
  nome_accessorio varchar(10)
  );
CREATE TABLE dbo.Dettagli_Auto (
  id_d int
    CONSTRAINT fk_Dettagli_Auto$has$Dettagli
      FOREIGN KEY REFERENCES dbo.Dettagli (id_d)
  ,
  id_a int
    CONSTRAINT fk_Dettagli_Auto$has$Auto
      FOREIGN KEY REFERENCES dbo.[Auto] (id_a)
  ,
  CONSTRAINT pk_Dettagli_Auto
    PRIMARY KEY (id_d, id_a)
  );


salutoni romagnoli
--
Andrea

Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php
404 messaggi dal 09 maggio 2012
Ciao, grazie per la riposta ...

il nome Audi ripetuto è normale. nel mio archivio ci sono migliaia di auto, anche stessa marca :D

ho provato ad utilizzare questa query:

SELECT *
FROM dbo.[Auto] a
JOIN dbo.Dettagli_Auto d ON d.id_a = a.id_a
WHERE d.id_accessorio = 44

... ma non va nè la prima per avere il totale di auto nè la seconda applicando uno o più filtri. questa è la query reale:

select a.marca, a.promo, a.stato, a.ID_a, a.tipo_annuncio, a.url, a.km_min, a.km_max, a.id_citta, a.id_comune, a.data_scadenza, a.gg, a.mm, a.aaaa, a.ora, a.minuti, a.anno, b.id_a, b.id_sottocat, b.descrizione from auto as a INNER JOIN Auto_dettagli as b ON a.ID_a=b.id_a where a.visibilita=@visibilita AND a.stato <> 0 order by a.ID_a DESC


in realtà la situazione è più complessa perchè dalla tabella dettaglio io voglio sapere quante auto hanno un id_accessorio=32 e nome_accessorio="airbag". questo perchè l'ID accessorio potrebbe essere 3, e sta per CV (cavalli) ... e voglio fare la where sul valore. ad esempio: ottieni tutte le auto che hanno almeno 200 cavalli. quindi il campo nome_accessorio è una stringa variabile

id_d, id_a, id_accessorio, nome_accessorio

1,1,32,airbag
2,1,44,fari xenon
3,2,43,abs
4,2,32,airbag
5,3,44,fari xenon
6,3,43,abs
7,4,32,airbag
8,5,44,fari xenon
9,5,43,abs
10,1,3,250
1.976 messaggi dal 27 luglio 2005
Contributi
aslve,

il nome Audi ripetuto è normale. nel mio archivio ci sono migliaia di auto, anche stessa marca :D

che il nome sia ripetuto, proprio normale non e' :D
la modellazione di questa realta' non e' affatto normalizzata.


SELECT *
    FROM dbo.[Auto] a
        JOIN dbo.Dettagli_Auto d ON d.id_a = a.id_a
    WHERE d.id_accessorio = 44;
... ma non va nè la prima per avere il totale di auto nè la seconda applicando uno o più filtri...


la query di proiezione indicata estrae tutte le auto con i relativi accessori che abbiano un accessorio con id_accessorio = 44 ad esse associato.
NON "conta" niente, proietta "*", quindi tutti gli attributi.
formalmente e' una query corretta, come lo e' anche logicamente per quanto ho indicato come risultato.

se si desidera un "conteggio", allora la proiezione sara' ovviamente diversa e dovra' prevedere ad esempio l'utilizzo della funzione COUNT relativa ad un GROUP BY di {auto}.id_a, {auto}.altro_attributo...


... questa è la query reale: ...


in questi casi, visto che tecnicamente chi voglia aiutare deve ricostruire il "tuo" scenario andando ad inventarsi struttura e dati, e' sempre meglio fornire il codice DDL (il piu' semplificato possibile) e qualche riga di INSERT INTO al fine di ottenere la ricostruzione di uno scenario piu' o meno completo...
in quanto
    from auto as a 
        INNER JOIN Auto_dettagli as b ON a.ID_a=b.id_a 
    where a.visibilita=@visibilita 
        AND a.stato <> 0 
    order by a.ID_a DESC

e' un filtro corretto per ottenere, come prima indicato, TUTTE le righe di Auto_dettagli che siano relezionabili alla tabella Auto, che soddisfino il parametro di @visibilita con stato <> 0.
se non ottieni il risultato desiderato andrebbero rivisti anche "i dati" in quanto formalmente tutto il predicato di cui sopra e' corretto.



in realtà la situazione è più complessa perchè dalla tabella dettaglio io voglio sapere quante auto hanno un id_accessorio=32 e nome_accessorio="airbag". questo perchè l'ID accessorio potrebbe essere 3, e sta per CV (cavalli) ... e voglio fare la where sul valore. ad esempio: ottieni tutte le auto che hanno almeno 200 cavalli. quindi il campo nome_accessorio è una stringa variabile

non ti insulto perche' NON sta bene :D:D:D

da come avevo precedentemente letto prima i tuoi dati, anche se tecnicamente sbagliato,
id_accessorio=32 == nome_accessorio="airbag"
costituiva una relazione univoca
se id_accessorio = 32 puo' poi essere con attributo nome_accessorio = "qualsiasi altra cosa", a questo punto tutta la amodellazione NON ha alcun senso...

id_accessorio = 32 -> nome_accessorio = "airbag TIPOA"
...
id_accessorio = 32 -> nome_accessorio = "airbag TIPOZ"
sarebbe veramente sbagliato una gestione di questo tipo... la modellazione e' realmente impropria...

dici poi, scrivi anche

questo perchè l'ID accessorio potrebbe essere 3, e sta per CV (cavalli) ... e voglio fare la where sul valore. ad esempio: ottieni tutte le auto che hanno almeno 200 cavalli


e poniamo che sia anche "validabile"
quindi
    ...
    where a.visibilita=@visibilita
        AND a.stato <> 0
        AND d.id_accessorio = 3
        AND d.nome_accessorio ????

in questo caso vuoi fare una verifica numerica su una valorizzazione tecnicamente testuale, quindi l'attributo DOVREBBE in questo caso contenere SOLO numeri [diversamente ti vengono sollevate eccezioni], che comporta il cast del valore a numerico e quindi verifica sull'operatore di confronto,

... AND CONVERT(int, d.nome_accessorio) >= @paramNumerico

in altri casi farai invece un confronto con CHARINDEX o altro...
... AND d.id_accessorio = 32 AND CHARINDEX(d.nome_accessorio,'TIPOZ') > 0 AND...
mi spiace e mi ripeto, la modellazione non e' corretta, e' prona a molteplici errori sia logici che per e nella popolazione dei dati

salutoni romagnoli
--
Andrea


Where is the wisdom? Lost in the knowledge.
Where is the knowledge? Lost in the information.
(T.S.Eliot)

Where is the information? Lost in the data.
Where is the data? Lost in the @%&#@ database.
(Joe Celko)

Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php
404 messaggi dal 09 maggio 2012
Ciao,
per quanto riguarda i nomi delle auto è un esempio. ovviamente nel mio database memorizzo l'ID della marca.

per quanto riguarda il confronto su un valore stringa ovviamente ho agito cosi:

"...AND b.id_sottocat=4 AND CONVERT(INT, CONVERT(DECIMAL(9, 0), ISNULL(b.descrizione, 0))) <= 250...."

devo necessariamente avere la colonna b.descrizione di tipo stringa .. perchè tutti i dati relativi alle automobili li ricevo mediante API REST in formato JSON. un valore potrebbe essere 250, un altro Airbag e cosi via. forse l'impostazione delle tabelle può essere migliorabile ma non credo sia del tutto sballata.

resta il fatto che ci sarà sicuramente un modo per capire quante auto ho che sono AUDI e hanno un dettaglio con id 24 e descrizione XX ... o dici che è impossibile?
1.976 messaggi dal 27 luglio 2005
Contributi

per quanto riguarda i nomi delle auto è un esempio. ovviamente nel mio database memorizzo l'ID della marca.

per quanto riguarda il confronto su un valore stringa ovviamente ho agito cosi:

"...AND b.id_sottocat=4 AND CONVERT(INT, CONVERT(DECIMAL(9, 0), ISNULL(b.descrizione, 0))) <= 250...."

devo necessariamente avere la colonna b.descrizione di tipo stringa .. perchè tutti i dati relativi alle automobili li ricevo mediante API REST in formato JSON. un valore potrebbe essere 250, un altro Airbag e cosi via. forse l'impostazione delle tabelle può essere migliorabile ma non credo sia del tutto sballata.


mi sono gia' espresso prima, quindi non mi ripeto


resta il fatto che ci sarà sicuramente un modo per capire quante auto ho che sono AUDI e hanno un dettaglio con id 24 e descrizione XX ... o dici che è impossibile?

certo che e' possibile...
devi trovare PRIMA la proiezione che soddisfa i tuoi filtri...
dopo di che basta effettuarne il raggruppamento ed esportarne solo il valore della funzione di COUNT
ma se continui a dirmi che il filtro che prima ti ho indicato NON funziona, allora prima bisogna verificarne il perche'...

se
SELECT *
    FROM dbo.[Auto] a
        JOIN dbo.Dettagli_Auto d ON d.id_a = a.id_a
    WHERE d.id_accessorio = 44;

funziona, ed ora vuoi sapere QUANTE sono le auto, allora basta

SELECT  a.marca
      , COUNT(*) AS [quantità]
    FROM dbo.[Auto] a
        JOIN dbo.Dettagli_Auto d ON d.id_a = a.id_a
    WHERE d.id_accessorio = 44
        -- AND d.xxxx = ????
    GROUP BY a.marca
    ORDER BY a.marca; 


salutoni romagnoli
--
Andrea


Where is the wisdom? Lost in the knowledge.
Where is the knowledge? Lost in the information.
(T.S.Eliot)

Where is the information? Lost in the data.
Where is the data? Lost in the @%&#@ database.
(Joe Celko)

Andrea Montanari
http://www.hotelsole.com - http://www.hotelsole.com/asql/index.php

Torna al forum | Feed RSS

ASPItalia.com non è responsabile per il contenuto dei messaggi presenti su questo servizio, non avendo nessun controllo sui messaggi postati nei propri forum, che rappresentano l'espressione del pensiero degli autori.