Remove From My Forums

Problem bei einer Abfrage

Frage
0

Anmelden
Guten morgen zusammen,

ich versuche erfolglos eine Abfrage zu schreiben und hoffe jemand kann mir helfen.

Es gibt die Tabellen Adressen, Stichworte und Stichworte2Adressen. Jeder Adresse können beliebig viele Stichworte zugewiesen werden. Dafür dient die Stichworte2Adressen Tabelle mit m:n Beziehung.

Über einem Suchdialog möchte ich nun Adressen finden welche Stichwörter zwingend, optional oder überhaupt nicht enthalten.

Mein erster Ansatz war nun:

SELECT DISTINCT A.* FROM Adressen A inner JOIN Stichworte2Adresse S ON A.Id = S.AdressId WHERE S.StichwortId = '719eafd0-6722-4146-bc01-a528bbed3b4e' AND S.StichwortId = '8c1c17a9-e7a9-44dc-907d-ae6b51742d6f' OR S.StichwortId = 'f624caad-8aea-4b13-af09-d63ef707c731' AND (StichwortId <> '0f68b2f5-bc8f-4d7a-b321-ae769b07f53c')

Ob die AND Klauseln alle korrekt greifen bin ich noch nicht sicher. Die NOT's stimmen auf jeden Fall nicht. Denn diese Abfrage:

SELECT DISTINCT A.* FROM Adressen A inner JOIN Stichworte2Adresse S ON A.Id = S.AdressId WHERE (StichwortId <> '0f68b2f5-bc8f-4d7a-b321-ae769b07f53c')

liefert mir Adressen, denen das Stichwort nicht zugeordnet ist. Aus Sicht der Datenbank stimmt das ja auch. Diese Ergebnisse sind aber gerade diese, die ich ausschließen möchte.

Ich hoffe ich konnte das Problem verständlich beschreiben...

Viele Grüße

Thorsten
- Bearbeitet eclere Dienstag, 22. Dezember 2015 06:25
Dienstag, 22. Dezember 2015 06:24

Antworten

|

Zitieren

Antworten

Anmelden

Jetzt wird auch die Fragestellung etwas deutlicher.
Du könntest entweder verschiedene EXISTS einbauen, oder die Anzahl zählen, wie Du es gemacht hast, oder wie es auch in meinem Beispiel hier gemacht habe:
http://www.insidesql.org/blogs/cmu/sql_server/pruefung-auf-mehrere-kriterien

Wenn Du jetzt noch andere Bedingungen hast, die mit OR verknüpft sein sollen, könntest Du dieses SQL separat erstellen und mit UNION die Vereinigungsmenge bilden.

Ich finde es einfacher die Kriterien in temporäre Tabellen (oder Tabellenvariablen) zu packen, als das SQL komplett neu zusammen zu bauen. Ist auch für die Ausführung praktischer, da die Abfrage immer das gleiche Statement und damit den gleichen Plan verwendet. Hier habe ich das mal an einem Beispiel durchgespielt.

DECLARE @Stichwort2Adresse AS TABLE (AdressId INT, StichwortId INT);

INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (1, 1);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (1, 2);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (1, 3);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (1, 5);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (2, 1);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (2, 2);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (2, 4);

DECLARE @Suchmaske_AND AS TABLE (StichwortId INT);
INSERT INTO @Suchmaske_AND(StichwortId) VALUES(1);
INSERT INTO @Suchmaske_AND(StichwortId) VALUES(2);
INSERT INTO @Suchmaske_AND(StichwortId) VALUES(3); -- Trifft bei Artikel 2 
nicht per AND zu

DECLARE @Suchmaske_OR AS TABLE (StichwortId INT);
INSERT INTO @Suchmaske_OR(StichwortId) VALUES(4); -- Hierüber kommt Artikel 
2 wieder dazu

-- AND Bedingungen
SELECT t1.AdressId, COUNT(*) AS Anzahl_AND, (SELECT COUNT(*) FROM
@Suchmaske_AND) as Zielwert_fuer_AND
FROM @Stichwort2Adresse t1
INNER JOIN @Suchmaske_AND a
     ON t1.StichwortId = a.StichwortId
GROUP BY t1.AdressId;

-- OR Bedingungen
Select Distinct AdressId as Adressen_mit_OR
from @Stichwort2Adresse t1
Where EXISTS(Select * from @Suchmaske_OR o where t1.StichwortId =
o.StichwortId);
 -- Prüfung mit CTE und Join und UNION
WITH Vorberechnung AS
(SELECT t1.AdressId, COUNT(*) AS Anzahl
FROM @Stichwort2Adresse t1
INNER JOIN @Suchmaske_AND a
     ON t1.StichwortId = a.StichwortId
GROUP BY t1.AdressId
)
SELECT DISTINCT AdressId
FROM Vorberechnung v
WHERE v.Anzahl = (SELECT COUNT(*) FROM @Suchmaske_AND)
UNION
Select Distinct AdressId
from @Stichwort2Adresse t1
Where EXISTS(Select * from @Suchmaske_OR o where t1.StichwortId =
o.StichwortId);
;

Einen schönen Tag noch,
Christoph
--
Data Platform MVP - http://www.insidesql.org/blogs/cmu

Als Antwort markiert eclere Donnerstag, 14. Januar 2016 15:03

Mittwoch, 23. Dezember 2015 07:51

Beantworter

0

Anmelden
Select into legt eine Tabelle an.
Du musst mit INSERT INTO @Tabellenvariable(<Feldliste>) Select <Feldliste> from <Tabelle> arbeiten.

Einen schönen Tag noch,
Christoph
--
Data Platform MVP - http://www.insidesql.org/blogs/cmu
- Als Antwort markiert eclere Donnerstag, 14. Januar 2016 15:03
Mittwoch, 13. Januar 2016 10:09

Antworten

|

Zitieren

Beantworter

Alle Antworten

Anmelden

Hallo Thorsten, ich weiß nicht, ob ich Deine Bedingungen richtig verstanden habe, aber dieser Ansatz sollte Dir weiterhelfen:

SELECT DISTINCT    A.*
FROM Adressen A
-- Zwingend zu erfüllen
WHERE EXISTS(Select * from Stichworte2Adresse S
     Where A.Id = S.AdressId
     AND    S.StichwortId = '719eafd0-6722-4146-bc01-a528bbed3b4e')
-- Optional wenigstens eine Bedingung zu erfüllen
AND EXISTS(Select * from Stichworte2Adresse S
     Where A.Id = S.AdressId
     AND    S.StichwortId in ('8c1c17a9-e7a9-44dc-907d-ae6b51742d6f',
'f624caad-8aea-4b13-af09-d63ef707c731'))
-- Darf nicht erfüllt sein
AND NOT EXISTS(Select * from Stichworte2Adresse S
     Where A.Id = S.AdressId
     AND    S.StichwortId    = '0f68b2f5-bc8f-4d7a-b321-ae769b07f53c');

Einen schönen Tag noch,
Christoph
--
Data Platform MVP - http://www.insidesql.org/blogs/cmu

Dienstag, 22. Dezember 2015 07:18

Beantworter

Anmelden

Hallo Thorsten,

vielleicht habe ich Dein Modell nicht richtig verstanden, aber um zu erreichen, dass eine Addresse beliebig viele Stichworte haben kann, brauchst Du keine dritte Tabelle.

LinqToSql Beispieltabellen :

[Table(Name="Addressen")]
private class Addresse
{
    [Column(IsPrimaryKey=true, IsDbGenerated=true)]
    public int Id;
    [Column]
    public string Addresse;
}

[Table(Name="Stichworte")]
private class Stichwort
{
    [Column(IsPrimaryKey=true, IsDbGenerated=true)]
    public int Id;
    [Column]
    public int AddresseId;
    [Column]
    public string Stichwort;
}

Grüße

Mentalist

Bearbeitet Mentalist999 Dienstag, 22. Dezember 2015 12:18

Dienstag, 22. Dezember 2015 12:14

0

Anmelden

Hallo Mentalist,
wie stellst Du dann sicher, dass nur bestimmte Stichworte verwendet werden?
Einen schönen Tag noch,
Christoph
--
Data Platform MVP - http://www.insidesql.org/blogs/cmu

Dienstag, 22. Dezember 2015 14:29

Antworten

|

Zitieren

Beantworter
0

Anmelden
Hallo zusammen,

vielen Dank für Eure bisherige Hilfe.

@Christoph: Ich habe mal etwas mit Deinem Code herumprobiert. So ganz klappt es nicht. Ich vermute mal ich meine Problemstellung nicht richtig rüber bringen konnte. Daher hier mal ein Screenshot der Suchmaske an der ich arbeite:

Der Benutzer soll hier Stichwörter beliebig auswählen können und die zugeordneten Adressen erhalten. Eine Mehrfachauswahl pro Zeile, also die gleichzeitige Auswahl von AND und NOT für ein Stichwort ist deaktiviert.

Wenn ich Deinen Code verwende funktioniert es besser als bei meiner Lösung, aber sobald bei zwei Stichwörtern AND aktiviert wurde erhalte ich kein Ergebnis. Hier mal ein Code, welcher eine Adresse liefern müsste:

SELECT DISTINCT A.* FROM Adressen A WHERE EXISTS(SELECT * from Stichworte2Adresse S WHERE a.Id = S.AdressId AND S.StichwortId ='29a7ab05-2552-4c0f-8a9e-d751b8299b57' AND S.StichwortId ='f0091216-dfe6-42c0-9d45-e40d1fa0e6b1')

Die OR und NOT Selektion habe ich erstmal weggelassen weil ich AND noch nicht hin bekommen habe.

@Mentalist: Die Stichworte selbst sind ebenfalls Stammdaten und einer Adresse sollen nicht alle Stichworte zugewiesen werden. Ich hoffe durch den Screenshot wird klarer was ich erreichen möchte.

Viele Grüße

Thorsten
Dienstag, 22. Dezember 2015 21:03

Antworten

|

Zitieren

Anmelden

Guten morgen,

ich bin etwas weiter: Die Anzahl der AND's ist mir ja bekannt. Also habe ich die Bedingung so verändert das die Ergebniszeilen meiner Anzahl der AND Bedingungen entsprechen muss.

SELECT DISTINCT A.* FROM Adressen A WHERE  
   (SELECT Count(Id) from Stichworte2Adresse S WHERE 
   S.AdressId=A.Id AND 
   S.StichwortId ='356faf67-404d-4c3a-850b-71bc60e0ca8e' OR S.StichwortId ='0f68b2f5-bc8f-4d7a-b321-ae769b07f53c'
   )=2

So geht es und ich habe mich schon gefreut...bis ich feststellte es hängt nun an den OR's. Und das ist, zumindest für mich, ziemlich seltsam.

SELECT DISTINCT A.* FROM Adressen A 
	WHERE
	(SELECT Count(Id) from Stichworte2Adresse S WHERE S.AdressId=A.Id	
		AND S.StichwortId ='356faf67-404d-4c3a-850b-71bc60e0ca8e' OR S.StichwortId ='0f68b2f5-bc8f-4d7a-b321-ae769b07f53c'
	)=2 
	AND 
	(SELECT COUNT(Id) from Stichworte2Adresse S WHERE S.AdressId=A.Id AND S.StichwortId ='71c592eb-71c6-4d8d-96f2-4b80569308fc'	
	)=1

Diese Abfrage liefert nun eine leere Menge. Füre ich beide Selects getrennt aus, bekomme ich das jeweils korrekte Ergebnis. Sie scheinen also zu stimmen, aber der Gesamtausdruck passt wohl noch nicht.

SELECT DISTINCT A.* FROM Adressen A WHERE 
	(SELECT COUNT(Id) from Stichworte2Adresse S WHERE S.AdressId=A.Id AND S.StichwortId ='71c592eb-71c6-4d8d-96f2-4b80569308fc'
	)>=1

Kann mir das Verhalten begründen?

Viele Grüße

Thorsten

Bearbeitet eclere Mittwoch, 23. Dezember 2015 07:33 Fehler beseitigt

Mittwoch, 23. Dezember 2015 07:06

Anmelden

Wenn Du jetzt noch andere Bedingungen hast, die mit OR verknüpft sein sollen, könntest Du dieses SQL separat erstellen und mit UNION die Vereinigungsmenge bilden.

DECLARE @Stichwort2Adresse AS TABLE (AdressId INT, StichwortId INT);

INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (1, 1);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (1, 2);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (1, 3);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (1, 5);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (2, 1);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (2, 2);
INSERT INTO @Stichwort2Adresse(AdressId, StichwortId) VALUES (2, 4);

DECLARE @Suchmaske_AND AS TABLE (StichwortId INT);
INSERT INTO @Suchmaske_AND(StichwortId) VALUES(1);
INSERT INTO @Suchmaske_AND(StichwortId) VALUES(2);
INSERT INTO @Suchmaske_AND(StichwortId) VALUES(3); -- Trifft bei Artikel 2 
nicht per AND zu

DECLARE @Suchmaske_OR AS TABLE (StichwortId INT);
INSERT INTO @Suchmaske_OR(StichwortId) VALUES(4); -- Hierüber kommt Artikel 
2 wieder dazu

-- AND Bedingungen
SELECT t1.AdressId, COUNT(*) AS Anzahl_AND, (SELECT COUNT(*) FROM
@Suchmaske_AND) as Zielwert_fuer_AND
FROM @Stichwort2Adresse t1
INNER JOIN @Suchmaske_AND a
     ON t1.StichwortId = a.StichwortId
GROUP BY t1.AdressId;

-- OR Bedingungen
Select Distinct AdressId as Adressen_mit_OR
from @Stichwort2Adresse t1
Where EXISTS(Select * from @Suchmaske_OR o where t1.StichwortId =
o.StichwortId);
 -- Prüfung mit CTE und Join und UNION
WITH Vorberechnung AS
(SELECT t1.AdressId, COUNT(*) AS Anzahl
FROM @Stichwort2Adresse t1
INNER JOIN @Suchmaske_AND a
     ON t1.StichwortId = a.StichwortId
GROUP BY t1.AdressId
)
SELECT DISTINCT AdressId
FROM Vorberechnung v
WHERE v.Anzahl = (SELECT COUNT(*) FROM @Suchmaske_AND)
UNION
Select Distinct AdressId
from @Stichwort2Adresse t1
Where EXISTS(Select * from @Suchmaske_OR o where t1.StichwortId =
o.StichwortId);
;

Einen schönen Tag noch,
Christoph
--
Data Platform MVP - http://www.insidesql.org/blogs/cmu

Als Antwort markiert eclere Donnerstag, 14. Januar 2016 15:03

Mittwoch, 23. Dezember 2015 07:51

Beantworter

0

Anmelden
@Thorsten

Also vielleicht verstehe ich das immernoch nicht richtig, aber in meinem Beispiel werden einer Addresse nicht alle Stichworte zugewiesen, sondern es gehören nur die Stichworte mit der passenden AddresseId auch zu der jeweiligen Addresse.

@Christoph

Falls ich Restriktionen habe nach denen ich meine Stichworte Filtern will, so würde ich dies in meiner LinqAbfrage tun.

Grobes Beispiel ohne gezielte Stichwortfilterung :

internal static List<string> StichworteRead(string Addresse, params int[] Ids) { List<string> Result = new List<string>(); IQueryable<string> Query; using (MyDataBase DB = new MyDataBase()) { if (Ids.Length == 0) { Query = from S in DB.Stichworte where S.AddresseId == DB.Addressen.Where(A => A.Text == Addresse).Select(A => A.Id).ToList()[0] select S.Text; } else { Query = from S in DB.Stichworte where Ids.Contains(S.Id) && S.AddresseId == DB.Addressen.Where(A => A.Text == Addresse).Select(A => A.Id).ToList()[0] select S.Text; } foreach(var S in Query) Result.Add(S); return Result; } }

Da würde ich dann eben meine Anforderung an die Stichworte, falls vorhanden, mit abfragen.

Geht sicherlich schöner, habe ich einfach mal auf die schnelle geschrieben.

Die Anforderung passt auch nicht ganz zu dem Modell mit dem ich arbeite, aber funktionieren sollte es so auf jedenfall und auf andere Modelle übertragbar sein sollte es auch. :)

Vorab schonmal Verzeihung, falls ich den Kern verfehlt haben sollte.

Grüße Mentalist

Ergänzung :

Man könnte in diesem Beispiel auch darüber nachdenken, die Stichwortfilterung in der letzten foreach abzuarbeiten, um weniger DB- und mehr Programmlastig zu arbeiten.

Diese Variante würde ich vermutlich verfolgen.
- Bearbeitet Mentalist999 Mittwoch, 23. Dezember 2015 08:26
Mittwoch, 23. Dezember 2015 08:20

Antworten

|

Zitieren
0

Anmelden

Ich dachte auch eher an eine Normalisierung der Tabellen und Foreign Key Constraints um die Datenintegrität sicherzustellen.

LINQ kommt dann zu spät ins Spiel.

Einen schönen Tag noch,
Christoph
--
Data Platform MVP - http://www.insidesql.org/blogs/cmu

Mittwoch, 23. Dezember 2015 10:50

Antworten

|

Zitieren

Beantworter
0

Anmelden
Guten Morgen,

die Feiertage sind nun rum und es geht weiter. Ich habe mit Deinem Code wirklich was anfangen können.

Einzig die Tabellenvariablen machen mich verrückt. Für mich ist eine Variable etwas das ich im Speicher ablege und nach meiner Methode wieder frei gebe. Der SQLServer legt diese jedoch auf meinem Server tatsächlich an und ich kann mir die auch im Management Studio oder so anschauen.

Eigentlich kein Problem, wenn diese sich auch wieder entfernen würde. Nach Ausführung meiner gespeicherten Prozedur bleiben diese aber bestehen. Beim erneuten Aufruf meiner Prozedur knallt es.

Ich habe mir dann so beholfen:

IF OBJECT_ID('Adressen..Suchmaske_AND') IS NOT NULL DROP TABLE Suchmaske_AND;

Diese Zeilen habe ich gleich zu Anfang eingefügt. Manchmal funktioniert dies aber nicht. Man muss dann die Prozedur einfach noch einmal ausführen und es geht wieder.

Außerdem stört mich die Angabe der Datenbank als Parameter. Das ist doof wenn ich mal einen anderen Datenbanknamen verwenden möchte. Dann ich ich die Prozedur anpassen.

Ich vermute hier irgendwo etwas falsch zu machen. Komme aber nicht drauf:

Viele Grüße

Thorsten
Mittwoch, 13. Januar 2016 06:08

Antworten

|

Zitieren
0

Anmelden

Hallo Thorsten,
Tabellenvariablen haben ein @ vor dem Namen. Du verwendest aber Tabellen. Diese werden persistiert.

Die Verwendung von permanenten Tabellen finde ich aber auch nicht schlecht. Du musst halt nur den Primary Key mit dem User erweitern und die Daten zu Anfang für den User löschen. Wenn die Suchmaske dann gefüllt ist, kannst Du sie verarbeiten.

Einen schönen Tag noch,
Christoph
--
Data Platform MVP - http://www.insidesql.org/blogs/cmu

Mittwoch, 13. Januar 2016 08:07

Antworten

|

Zitieren

Beantworter

Anmelden

Hallo,

hmm, ich habe ein @davor. Zumindest bei der Initialisierung;

DECLARE @Suchmaske_AND AS TABLE (AdressID uniqueidentifier, StichwortID uniqueidentifier, AdressNummer NVARCHAR(40), LKZ nvarchar(5), PLZ nvarchar(7));

Bei Abfragen lasse ich dieses jedoch weg, da ich sonst immer folgenden Fehler bekomme:

Meldung 102, Ebene 15, Status 1, Prozedur FindBySW, Zeile 89
Falsche Syntax in der Nähe von '@Suchmaske_AND'.

Noch mal im Code:

--Funktioniert
SELECT A.Id as AdressID, newid() as StichwortID, A.AdressNummer, A.Laenderkennzeichen as LKZ, A.Postleitzahl as PLZ            INTO Suchmaske_AND            FROM Adressen A

--Funtioniert nicht
SELECT A.Id as AdressID, newid() as StichwortID, A.AdressNummer, A.Laenderkennzeichen as LKZ, A.Postleitzahl as PLZ            INTO @Suchmaske_AND            FROM Adressen A

Gruß Thorsten

Mittwoch, 13. Januar 2016 08:48

0

Anmelden

Ich sehe gerade dass "select into" mit Tabellenvariablen gar nicht funktioniert.

Dann muss ich wohl doch mit physischen Tabellen arbeiten.

Gruß Thorsten

Mittwoch, 13. Januar 2016 09:26

Antworten

|

Zitieren
0

Anmelden
Select into legt eine Tabelle an.
Du musst mit INSERT INTO @Tabellenvariable(<Feldliste>) Select <Feldliste> from <Tabelle> arbeiten.

Einen schönen Tag noch,
Christoph
--
Data Platform MVP - http://www.insidesql.org/blogs/cmu
- Als Antwort markiert eclere Donnerstag, 14. Januar 2016 15:03
Mittwoch, 13. Januar 2016 10:09

Antworten

|

Zitieren

Beantworter
0

Anmelden

Vielen Dank für die Geduld. So funktioniert's.

Viele Grüße

Thorsten

Donnerstag, 14. Januar 2016 15:03

Antworten

|

Zitieren

Produkte

Resources

Solutions

Updates

Testversionen

Verwandte Websites

Training

Zertifizierungen

Weitere Ressourcen

Für Produkte

Mehr Support

Kein IT-Experte?

Benutzer mit den meisten Antworten

Problem bei einer Abfrage

Frage

Antworten

Alle Antworten