Aiuto per una query

mercoledì 01 febbraio 2012 - 19.57
Tag Elenco Tags  VB.NET

zseven Profilo | Senior Member

Ciao ragazzi,
non riesco a trovare una soluzione per questo problema:
in pratica ho tre tabelle:
Elementi
Servizi
Elementi_servizi

La tabella elementi_servizi è una tabella intermedia dove vado ad inserire gli id delle altre due tabelle per associare ad uno specifico elemento uno o più servizi.

Immaginate ad esempio che un Utente clicca su 2 o più checkbox per ottenere come risultato un Hotel che ha come servizi sia l'ascensore che la discesa a mare.

Ho provato diverse soluzioni ma non sono riuscito a venirne a capo completamente.

Prima di tutto mi chiedo: E' una cosa che posso risolvere solo con una UNION per ogni singolo servizio selezionato?

Qui sotto vi scrivo le CREATE per le tre tabelle forse possono esservi utili per aiutarmi:
CREATE TABLE [dbo].[tab_elementi]( [ID_elementi] [int] IDENTITY(1,1) NOT NULL, [nome_elemento] [nvarchar](max) NULL, ) ON [PRIMARY] '************ CREATE TABLE [dbo].[tab_servizi]( [ID_servizi] [int] IDENTITY(1,1) NOT NULL, [nome_servizio] [nvarchar](max) NULL, [icona_servizio] [nvarchar](max) NULL, [elimina_servizio] [bit] NULL ) ON [PRIMARY] '************** CREATE TABLE [dbo].[tab_servizi_elementi]( [ID_servizi_elementi] [int] IDENTITY(1,1) NOT NULL, [id_elemento] [numeric](18, 0) NOT NULL, [id_servizio] [numeric](18, 0) NOT NULL, [attiva_servizio_elemento] [bit] NULL ) ON [PRIMARY]


io ho fatto una query, ma credo sia eccessivamente complicata e poi comunque non ottengo il risultato preciso:
Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

Vedete in questo caso ho considerato anche un'ulteriore tabella tab_Citta, ma come risultato se quello specifico hotel ha quei 2 o più servizi viene conteggiato più volte, e non riesco a risolvere con un DISTINCT.

Spero di essere stato chiaro, e soprattutto che riusciate a darmi una mano.
Grazie mille
Guido

lbenaglia Profilo | Guru

>Spero di essere stato chiaro, e soprattutto che riusciate a darmi
>una mano.

Ciao Guido,

Per esserlo dovresti postare delle righe di prova per le 3 tabelle (INSERT INTO) ed il restult set atteso.

>Grazie mille
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

zseven Profilo | Senior Member

Ciao Lorenzo,
spero di spiegartelo nel modo migliore, anche perchè non saprei come altro fare.


tab_citta:
INSERT INTO tab_citta(nome_citta) VALUES("Milano")

tab_elementi:
INSERT INTO tab_elementi(nome_elemento, id_citta) VALUES("Hotel Uno", 1)

tab_servizi
INSERT INTO tab_servizi(nome_servizio) VALUES("Accesso al mare")
INSERT INTO tab_servizi(nome_servizio) VALUES("Ascensore")

tab_servizi_elementi
INSERT INTO tab_servizi(id_servizio, id_elemento) VALUES(1, 1)
INSERT INTO tab_servizi(id_servizio, id_elemento) VALUES(2, 1)

Se adesso un utente effettua una ricerca cercando gli hotel a MILANO e spunta da un checkboxlist sia l'accesso al mare che l'ascensore mi deve uscire come risultato solo una volta "hotel uno" e ovviamente la città richiesta.
Dalla query di esempio che ti ho scritto (che credo sia completamente sbagliata) ottengo due volte hotel uno

Spero che questi miei esempi vadano bene.
Grazie mille
Guido

schumy2000 Profilo | Junior Member

E' ovvio che ti compare 2 volte...a meno che non metti la clausola group by
-------------------------------------------------------------------------------------------------------------
Talvolta un pensiero mi annebbia l'Io: sono pazzi gli altri o sono pazzo io?
A. Einstein

zseven Profilo | Senior Member

ho provato anche con la clausola GROUP BY, ma se metto solo soltanto il campo che devo raggruppare mi dà errore perchè dice che gli altri non sono inclusi nella funzione di aggregazione.
Nel momento in cui li vado ad aggiungere ottengo lo stesso risultato di prima.

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

schumy2000 Profilo | Junior Member

Ovvio, il group by raggruppa le cose uguali se trova anche un campo diverso non lo raggruppa.... quello che chiedi tu è praticamente impossibile
-------------------------------------------------------------------------------------------------------------
Talvolta un pensiero mi annebbia l'Io: sono pazzi gli altri o sono pazzo io?
A. Einstein

zseven Profilo | Senior Member

per te è tutto ovvio

Grazie comunque per l'aiuto, credo ci sia un modo per ottenere il risultato, le tabelle dovrebbero essere impostate in maniera corretta come relazioni, e devo ottenere semplicemente quegli elementi singoli che hanno le caratteristiche indicate nella tabella intermedia.

Spero qualcuno possa aiutarmi, magari suggerendomi anche altre strade rispetto alla query che avevo avviato, che, ripeto, mi sembra un pò troppo complessa.
Avevo anche provato con un WHERE IN, ma in quel caso mi restituiva la corrsipondenza anche di un solo elemento, e non di 2 o più elementi contemporaneamente.

lbenaglia Profilo | Guru

>Se adesso un utente effettua una ricerca cercando gli hotel a
>MILANO e spunta da un checkboxlist sia l'accesso al mare che
>l'ascensore mi deve uscire come risultato solo una volta "hotel
>uno" e ovviamente la città richiesta.

Quello che chiedi lo ottieni con la seguente query:

USE tempdb; CREATE TABLE dbo.elementi( id_elemento int IDENTITY NOT NULL, nome_elemento varchar(10) NULL, id_città int NOT NULL, CONSTRAINT PK_Elementi PRIMARY KEY(id_elemento) ); CREATE TABLE dbo.servizi( id_servizio int IDENTITY NOT NULL, nome_servizio varchar(20) NULL, icona_servizio varchar(5) NULL, elimina_servizio bit NULL, CONSTRAINT PK_Servizi PRIMARY KEY(id_servizio) ); CREATE TABLE dbo.servizi_elementi( id_elemento int NOT NULL, id_servizio int NOT NULL, attiva_servizio_elemento bit NULL, CONSTRAINT PK_Servizi_Elementi PRIMARY KEY(id_elemento, id_servizio), CONSTRAINT FK_servizi_elementi_elementi FOREIGN KEY(id_elemento) REFERENCES dbo.elementi(id_elemento), CONSTRAINT FK_servizi_elementi_servizi FOREIGN KEY(id_servizio) REFERENCES dbo.servizi(id_servizio) ); INSERT dbo.elementi VALUES('Hotel Uno', 1); INSERT INTO dbo.servizi(nome_servizio) VALUES('Accesso al mare'), ('Ascensore'); INSERT INTO servizi_elementi(id_servizio, id_elemento) VALUES(1, 1), (2, 1); SELECT E.nome_elemento, E.id_città FROM dbo.elementi AS E JOIN dbo.servizi_elementi AS SE ON E.id_elemento = SE.id_elemento JOIN dbo.servizi AS S ON SE.id_servizio = S.id_servizio WHERE S.nome_servizio IN ('Accesso al mare', 'Ascensore') AND E.id_città = 1 GROUP BY E.nome_elemento, E.id_città; /* Output: nome_elemento id_città ------------- ----------- Hotel Uno 1 (1 row(s) affected) */ DROP TABLE dbo.servizi_elementi, dbo.servizi, dbo.elementi;

Manca la definizione della tabella Città (non l'hai postata) ma la logica è quella.

>Grazie mille
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

zseven Profilo | Senior Member

Ciao Lorenzo,
grazie mille ma purtroppo così non ottengo esattamente ciò che mi serve, in quanto con la clausola WHERE IN mi restituisce quegli elementi per i quali trova corrispondenza anche uno solo degli elementi presenti.
Invece io ho la necessità di trovare gli elementi per i quali sono presenti tutti i servizi selezionati.
Nel caso specifico dell'esempio che mi hai scritto devo trovare tutti gli elementi che hanno sia l'accesso al mare che l'ascensore, e provando per milano mi restituisce una riga, ma solo perchè trova corrispondenza con l'ascensore, visto che a milano è un pò difficile trovare l'accesso al mare :D

Questo tipo di query infatti è stato il primo cheho provato, poi dopo sono passato a provare con la union pensando potesse essere l'unica soluzione, ma incappavo nella ripetizione dei record.

Spero tu riesca ad aiutarmi, grazie mille!
Guido

lbenaglia Profilo | Guru

>Ciao Lorenzo,
>grazie mille ma purtroppo così non ottengo esattamente ciò che
>mi serve, in quanto con la clausola WHERE IN mi restituisce quegli
>elementi per i quali trova corrispondenza anche uno solo degli
>elementi presenti.

Tu hai scritto: "mi deve uscire come risultato solo una volta "hotel uno" e ovviamente la città richiesta".
la query che ti ho indicato restituisce questo risultato.

>Invece io ho la necessità di trovare gli elementi per i quali
>sono presenti tutti i servizi selezionati.
Quali?
Con l'esempio completo che ti ho allegato, indica il result set che vorresti ottenere.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

zseven Profilo | Senior Member

Evidentemente non mi sono spiegato bene:
"mi deve uscire come risultato solo una volta "hotel uno" e ovviamente la città richiesta".
questo che ho detto è ancora valido, il punto è che bisogna anche tenere conto della selezione effettuata.

Come avevo indicato nel post se un utente seleziona sia "Accesso al mare" che "Ascensore" deve restituirmi una sola volta l'elemento che ha ENTRAMBI i servizi.

Nell'esempio che mi hai gentilmente scritto invece restituisce il risultato anche per uno soltanto dei servizi.
Infatti mi dà come risultato un elemento al quale è associato l'ascensore, ma non l'accesso al mare, e quindi non va bene.

Spero di essermi spiegato meglio.
Grazie mille
Guido

lbenaglia Profilo | Guru

>Come avevo indicato nel post se un utente seleziona sia "Accesso
>al mare" che "Ascensore" deve restituirmi una sola volta l'elemento
>che ha ENTRAMBI i servizi.

Ah, ho capito. Guarda questo esempio:

USE tempdb; CREATE TABLE dbo.elementi( id_elemento int IDENTITY NOT NULL, nome_elemento varchar(10) NULL, id_città int NOT NULL, CONSTRAINT PK_Elementi PRIMARY KEY(id_elemento) ); CREATE TABLE dbo.servizi( id_servizio int IDENTITY NOT NULL, nome_servizio varchar(20) NULL, icona_servizio varchar(5) NULL, elimina_servizio bit NULL, CONSTRAINT PK_Servizi PRIMARY KEY(id_servizio) ); CREATE TABLE dbo.servizi_elementi( id_elemento int NOT NULL, id_servizio int NOT NULL, attiva_servizio_elemento bit NULL, CONSTRAINT PK_Servizi_Elementi PRIMARY KEY(id_elemento, id_servizio), CONSTRAINT FK_servizi_elementi_elementi FOREIGN KEY(id_elemento) REFERENCES dbo.elementi(id_elemento), CONSTRAINT FK_servizi_elementi_servizi FOREIGN KEY(id_servizio) REFERENCES dbo.servizi(id_servizio) ); INSERT dbo.elementi VALUES('Hotel Uno', 1); INSERT INTO dbo.servizi(nome_servizio) VALUES('Accesso al mare'), ('Ascensore'); INSERT INTO servizi_elementi(id_servizio, id_elemento) VALUES(1, 1), (2, 1); /* Numero di servizi selezionati */ DECLARE @Count int = 2; SELECT E.nome_elemento, E.id_città FROM dbo.elementi AS E JOIN dbo.servizi_elementi AS SE ON E.id_elemento = SE.id_elemento JOIN dbo.servizi AS S ON SE.id_servizio = S.id_servizio WHERE S.nome_servizio IN ('Accesso al mare', 'Ascensore') AND E.id_città = 1 GROUP BY E.nome_elemento, E.id_città HAVING COUNT(*) = @Count; /* Output: nome_elemento id_città ------------- ----------- Hotel Uno 1 (1 row(s) affected) */ DROP TABLE dbo.servizi_elementi, dbo.servizi, dbo.elementi;

Come puoi vedere è identico al precedente con l'aggiunta della clausola HAVING che fa in modo di restituire solo gli hotel per i quali esistano tutti i servizi selezionati dall'utente (e specificati nella clausola IN()).

>Grazie mille
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

zseven Profilo | Senior Member

Ottimo funziona perfettamente!
GRAZIE MILLE!!!!
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2017
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5