Forum: PC-Programmierung Ist so eine SQL-Abrage mit einem SQL-Befehl möglich?


Announcement: there is an English version of this forum on EmbDev.net. Posts you create there will be displayed on Mikrocontroller.net and EmbDev.net.
von Uhu U. (uhu)


Bewertung
0 lesenswert
nicht lesenswert
Ich habe u.a. diese 2 Tabellen in einer SQLITE-Datenbank:
P: (_id INTEGER PRIMARY KEY AUTOINCREMENT, ...);
A: (_id INTEGER PRIMARY KEY AUTOINCREMENT, ParentId INTEGER, ParentTab INTEGER, ...)

A.ParentId ist eine _id in P, wenn das Feld ParentTab eine 0 enthält und
A.ParentId ist eine _id in A, wenn das Feld ParentTab eine 1 enthält.

Kann man mit SQL eine Abfrage bauen, die alle Einträge in A findet, die 
auf einen bestimmten Satz in P verweisen und alle Sätze in A, die auf 
einen Satz in A verweisen, der auf einen Satz in P verweist?

Die Sätze der Ergebnismenge sollen so sortiert sein, dass die A-Sätze, 
die auf einen A-Satz verweisen, direkt nach dem A-Satz kommen, auf den 
sie verweisen.

Die Schachtelungstiefe in A ist 1 - es gibt also keine Sätze, die auf 
einen Satz in A verweisen, der auf einen Satz in A verweist.

: Bearbeitet durch User
von T.roll (Gast)


Bewertung
-1 lesenswert
nicht lesenswert
Uhu U. schrieb:
> Kann man mit SQL eine Abfrage bauen, die alle Einträge in A findet, die
> auf einen bestimmten Satz in P verweisen und alle Sätze in A, die auf
> einen Satz in A verweisen, der auf einen Satz in P verweist?

Ja kann man.

von Frank L. (Firma: Flk Consulting UG) (flk)


Bewertung
1 lesenswert
nicht lesenswert
Hallo
Ja geht, such mal nach nested select bei Tante Google

Gruß
Frank

von Sheeva P. (sheevaplug)


Bewertung
0 lesenswert
nicht lesenswert
Uhu U. schrieb:
> Ich habe u.a. diese 2 Tabellen in einer SQLITE-Datenbank:
>
>
> P: (_id INTEGER PRIMARY KEY AUTOINCREMENT, ...);
> A: (_id INTEGER PRIMARY KEY AUTOINCREMENT, ParentId INTEGER, ParentTab 
> INTEGER, ...)
> 
>
> A.ParentId ist eine _id in P, wenn das Feld ParentTab eine 0 enthält und
> A.ParentId ist eine _id in A, wenn das Feld ParentTab eine 1 enthält.
>
> Kann man mit SQL eine Abfrage bauen, die alle Einträge in A findet, die
> auf einen bestimmten Satz in P verweisen und alle Sätze in A, die auf
> einen Satz in A verweisen, der auf einen Satz in P verweist?

Das sieht nach einer Art Baumstruktur aus, vielleicht einem Menü. Hast 
Du Gründe dafür, warum Du dazu zwei Tabellen verwendest? Normalerweise 
würde dafür doch eine Tabelle ausreichen, wie in diesem Beispiel [1].

Ansonsten hilft SQL-Meister Joe Celko in [2] und ausführlicher in [3].

[1] http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
[2] 
http://www.ibase.ru/files/articles/programming/dbmstrees/sqltrees.html
[3] 
https://datubaze.files.wordpress.com/2016/03/celkos-trees-and-hierarchies-in-sql_2004.pdf

von Uhu U. (uhu)


Bewertung
0 lesenswert
nicht lesenswert
Sheeva P. schrieb:
> Hast Du Gründe dafür, warum Du dazu zwei Tabellen verwendest?

Ja. Die P-Tabelle soll Kollektionen von A-Einträgen benennen. In 
weiteren Tabellen werden diese Kollektionen dann über die P-Einträge 
referiert. Das ist nur ein sehr rudimentärer Baum.

T.roll schrieb:
> Ja kann man.

So weit bin ich mittlerweile auch. Nur wie die Select-Anweisung konkret 
aussehen muss, habe ich noch nicht herausgefunden. Hab mich erst mal mit 
Sqliteman rumgeärgert, bis ich herausgefunden hatte, wie die Eingabe 
funktioniert...

: Bearbeitet durch User
von Frank L. (Firma: Flk Consulting UG) (flk)


Bewertung
0 lesenswert
nicht lesenswert
Hallo,
Fang damit an, das Du die einzelnen Fragestellungen in jeweils einer 
Select Anweisung formulierst.

Versuch noch nicht das Gesamtproblem in einem Rutsch zu lösen.

Im zweiten Schritt kombinierst Du Deine vorherigen Lösungen durch Joins 
zu einer Gesamtlösung.

Wenn Du die einzelnen Schritte postest kann ich schauen wo ich Dir 
helfen kann.

Gruß
Frank

von Uhu U. (uhu)


Bewertung
0 lesenswert
nicht lesenswert
Frank L. schrieb:
> Wenn Du die einzelnen Schritte postest kann ich schauen wo ich Dir
> helfen kann.

Das ist nett.

Das ist meine Spielwiese:
INSERT INTO "P" VALUES(1, ...);
INSERT INTO "P" VALUES(2, ...);
                       \
                        \
                         \ ParentId (Spalte 2)
INSERT INTO "A" VALUES(1, 1, 0, ...);
INSERT INTO "A" VALUES(2, 1, 0, ...);
INSERT INTO "A" VALUES(3, 2, 0, ...);
INSERT INTO "A" VALUES(4, 2, 0, ...);
                       \
                        \     ParentTab (Spalte 3)
                         \   /
INSERT INTO "A" VALUES(5, 4, 1, ...);
INSERT INTO "A" VALUES(6, 4, 1, ...);
Die Sätze 5 und 6 hängen an Satz 4; 1 bis 4 hängen an den Sätzen 1 und 2 
in Tabelle P

Die Abfrage auf ParentId=1 soll A.1 und A.2 liefern,
die Abfrage auf ParentId=2 soll A.3, A.4, A.5, A.6 (in der Reihenfolge) 
liefern.

Die Einzelabfragen sind ja noch schön einfach:
SELECT * FROM A WHERE ParentId=2 AND ParentTab=0;
SELECT a2.* FROM A AS a1 JOIN A AS a2 ON a1._id=a2.ParentId WHERE a1.ParentId=2 AND a1.ParentTab=0;

Die 1. Abfrage bringt A.3 und A.4
die 2. Abfrage bringt A.5 und A.6
SELECT * FROM A WHERE ParentId=2 AND ParentTab=0
UNION
SELECT a2.* FROM A AS a1 JOIN A AS a2 ON a1._id=a2.ParentId WHERE a1.ParentId=2 AND a1.ParentTab=0;
vereinigt die beiden Resultat-Sets.

: Bearbeitet durch User
von Frank L. (Firma: Flk Consulting UG) (flk)


Bewertung
0 lesenswert
nicht lesenswert
Hey
Ich schaue es mir morgen Vormittag mal
Gruß
Frank

von Uhu U. (uhu)


Bewertung
0 lesenswert
nicht lesenswert
Spaßeshalber: könnte man die Schachtelung in A auch grenzenlos machen 
und die A-Sätze, die beliebig tief geschachtelt sind auch mit einem 
einzigen SQL,Befehl so herausangeln?

von Jim M. (turboj)


Bewertung
0 lesenswert
nicht lesenswert
Uhu U. schrieb:
> beliebig tief geschachtelt sind auch mit einem
> einzigen SQL,Befehl so herausangeln?

SQL ist (absichtlich) nicht turing-mächtig. Da geht "beliebig tief" IMO 
nicht ohne weiteres.

Bedenke dass Du im obigen Beispiel für jede weitere Ebene ein 
zusätzliches JOIN einfügen müsstest.

von Frank L. (Firma: Flk Consulting UG) (flk)


Bewertung
0 lesenswert
nicht lesenswert
Hallo,
Mit einer Rahmenbedingung kann eine SQL-Datenbank einen Baum mit 
beliebiger Tiefe und einem Select rekursiv durchlaufen.

Die Denksportaufgabe ist er öffnet :-)

Tip: Es ist keine OO-DB... Es geht nicht mit jeder relationalen DB

Gruß
Frank

von Frank L. (Firma: Flk Consulting UG) (flk)


Bewertung
0 lesenswert
nicht lesenswert
Uhu U. schrieb:
> Frank L. schrieb:
>> Wenn Du die einzelnen Schritte postest kann ich schauen wo ich Dir
>> helfen kann.
>
> Das ist nett.
>
> Das ist meine Spielwiese:
>
> INSERT INTO "P" VALUES(1, ...);
> INSERT INTO "P" VALUES(2, ...);
>                        \
>                         \
>                          \ ParentId (Spalte 2)
> INSERT INTO "A" VALUES(1, 1, 0, ...);
> INSERT INTO "A" VALUES(2, 1, 0, ...);
> INSERT INTO "A" VALUES(3, 2, 0, ...);
> INSERT INTO "A" VALUES(4, 2, 0, ...);
>                        \
>                         \     ParentTab (Spalte 3)
>                          \   /
> INSERT INTO "A" VALUES(5, 4, 1, ...);
> INSERT INTO "A" VALUES(6, 4, 1, ...);
> 
> Die Sätze 5 und 6 hängen an Satz 4; 1 bis 4 hängen an den Sätzen 1 und 2
> in Tabelle P
>
> Die Abfrage auf ParentId=1 soll A.1 und A.2 liefern,
> die Abfrage auf ParentId=2 soll A.3, A.4, A.5, A.6 (in der Reihenfolge)
> liefern.
>
> Die Einzelabfragen sind ja noch schön einfach:
>
> SELECT * FROM A WHERE ParentId=2 AND ParentTab=0;
> SELECT a2.* FROM A AS a1 JOIN A AS a2 ON a1._id=a2.ParentId WHERE 
> a1.ParentId=2 AND a1.ParentTab=0;
> 
>
> Die 1. Abfrage bringt A.3 und A.4
> die 2. Abfrage bringt A.5 und A.6
>
>
> SELECT * FROM A WHERE ParentId=2 AND ParentTab=0
> UNION
> SELECT a2.* FROM A AS a1 JOIN A AS a2 ON a1._id=a2.ParentId WHERE 
> a1.ParentId=2 AND a1.ParentTab=0;
> 
> vereinigt die beiden Resultat-Sets.

Hallo,
Dein Beispiel weicht schon von Deinem Anfangspost ab.

Du schreibst "A.ParentId ist eine _id in P, wenn das Feld ParentTab eine 
0 enthält" bitte erkläre nochmal genau was Du willst.

Willst Du hinterher eine sortierte Menge von Sätzen haben, die alle 
Werte umfasst oder interessieren Dich tatsächlich nur die Menge für ein 
konkretes Tab?

Gruß
Frank

von Frank L. (Firma: Flk Consulting UG) (flk)


Bewertung
0 lesenswert
nicht lesenswert
Sorry meinte ParentId

von Uhu U. (uhu)


Bewertung
0 lesenswert
nicht lesenswert
Frank L. schrieb:
> Du schreibst "A.ParentId ist eine _id in P, wenn das Feld ParentTab eine
> 0 enthält" bitte erkläre nochmal genau was Du willst.

ParentId kann zwei verschiedene Arten von Verweisen enthalten:
  - wenn ParentTab == 0 zeigt ParentId in die Tabelle P
  - wenn ParentTab == 1 zeigt ParentId in die Tabelle A

Zu jedem Satz in P gehören n Sätze in A.                (ParentTab=0)
Zu jedem Satz in A können m weitere Sätze in A gehören. (ParentTab=1)

Die Abfrage soll die Sätze so liefern:
  A.3
  A.4
   A.5
   A.6
(Die Einrückung soll nur deutlich machen, dass 5 und 6 zu 4 gehören.)

Falls noch ein Satz A.7 mit ParentId=2 und ParentTab=0 folgen würde, 
müsste der nach A.6 kommen, usw.

: Bearbeitet durch User
von Frank L. (Firma: Flk Consulting UG) (flk)


Bewertung
0 lesenswert
nicht lesenswert
Hallo,

so, schauen wir mal.

Ausgehend von:

A.ParentId ist eine _id in P, wenn das Feld ParentTab eine 0 enthält

Schritt 1: Alle Elemente in a deren ParentTab eine 0 enthält, wir 
selektieren direkt ParentId für die nächste Kombination.
SELECT Parentid FROM A WHERE ParentTab = 0

Schritt 2: und ein Element von P sind
SELECT _id FROM P WHERE _id in (SELECT Parentid FROM A WHERE ParentTab = 0)

Schritt 3: zur Prüfung alle _id in A
SELECT _id FROM A WHERE ParentId IN (SELECT _id FROM P WHERE _id in (SELECT Parentid FROM A WHERE ParentTab = 0));

Ergebnis: 1, 2, 3, 4 in A

------------------------------------------------------------------------

Zweiter Teil: A.ParentId ist eine _id in A, wenn das Feld ParentTab eine 
1 enthält.

Schritt 1: Alle a für ParentTab = 1
SELECT ParentId FROM A where ParentTab = 1

Schritt 2: Alle A für die das zutrifft
SELECT _id FROM A WHERE ParentId IN (SELECT ParentId FROM A where ParentTab = 1);

Ergebnis: 5, 6

Jetzt noch das Ganze zu einer allgemeine Gesamtabfrage kombinieren.
SELECT * FROM A WHERE _id IN (SELECT _id FROM A WHERE ParentId IN (SELECT _id FROM P WHERE _id in (SELECT Parentid FROM A WHERE ParentTab = 0)) UNION SELECT _id FROM A WHERE ParentId IN (SELECT ParentId FROM A where ParentTab = 1));

Ergebnis: 1, 2, 3, 4, 5, 6

------------------------------------------------------------------------

Letzlich hast Du damit alle gültigen Kombinationen Deiner Werte.

Würdest Du jetzt einen neuen Datensatz mit den Daten:
INSERT INTO a (_id, ParentId, ParentTab) VALUES (7,13,0)

einfügen, würde dieser nicht in der Gesamtabfrage auftauchen, da er 
nicht korrekt ist.

Wenn Du jetzt alle Werte haben willst, die direkt oder in direkt an 
einer ParentId hängen, würdest Du die Abfrage so gestalten:
SELECT * FROM A WHERE _id IN (SELECT _id FROM A WHERE ParentId IN (SELECT _id FROM P WHERE ParentId = 2 AND _id in (SELECT Parentid FROM A WHERE ParentTab = 0)) UNION SELECT _id FROM A WHERE ParentId IN (SELECT ParentId FROM A where ParentTab = 1));

Ergebnis: 3, 4, 5, 6

Das Ganze ist in keiner Form performanzoptimiert abgebildet. Es soll nur 
veranschaulichen wie es geht. Performanztechnisch ist diese Formulierung 
die Hölle und nicht für große Datenmengen geeignet.

Gruß
Frank

: Bearbeitet durch User
von Frank L. (Firma: Flk Consulting UG) (flk)


Bewertung
0 lesenswert
nicht lesenswert
Der Knackpunkt an dieser Abfrage ist die Kombination von UNION und dem 
zweiten Teil Deiner Frage "A.ParentId ist eine _id in A, wenn das Feld 
ParentTab eine 1 enthält." Hier durch werden 5 und 6 immer dazu 
gemischt.

Das muss ich mir nochmal näher anschauen.

Gruß
Frank

von Uhu U. (uhu)


Bewertung
0 lesenswert
nicht lesenswert
Frank L. schrieb:
> Ergebnis: 1, 2, 3, 4, 5, 6

Das ist nicht, was ich haben will.

Eine Abfrage für P._id=2 soll folgendes ergeben:
1. alle Sätze in A mit ParentId=2 und ParentTab=0 und
2. alle Sätze in A mit ParentId=<eine _id aus dem ersten teil der abfrage> und ParentId=1

Die Sätze aus 2. sollen jeweils nach dem A-Satz kommen, auf den sie 
verweisen. (Vermutlich muss ich da noch eine Sequenznummer einbauen und 
das Ergebnis danach sortieren.)

: Bearbeitet durch User
von Frank L. (Firma: Flk Consulting UG) (flk)


Bewertung
0 lesenswert
nicht lesenswert
Sorry,
Das hast Du nicht geschrieben s. Eingangspost und folgende.

Gruß
Frank

von Uhu U. (uhu)


Bewertung
0 lesenswert
nicht lesenswert
Du hast Recht, die Beschreibung im Eingangsposting ist nicht korrekt.

Es müsste korrekt heißen:
1. alle Einträge in A, die auf einen bestimmten Satz in P verweisen und
2. alle Sätze in A, die auf einen Satz in A verweisen, der auf einen 
Resultatsatz aus 1. verweist.

Beitrag "Re: Ist so eine SQL-Abrage mit einem SQL-Befehl möglich?" beschreibt es 
korrekt.

Ich komme immer wieder zu dem Resultat, dass meine Anfrage mit der UNION 
das leistet. Ich habs in Sqliteman getestet, es läuft zumindest auf der 
Mini-DB, die ich ihm zu fressen gegeben habe.

: Bearbeitet durch User
von SQL (Gast)


Bewertung
0 lesenswert
nicht lesenswert
select * from a left outer join p on a.parentid=p.id and a.parenttab=1 
order by a.id,a.parenttab,a.parentid

von Ergo70 (Gast)


Bewertung
0 lesenswert
nicht lesenswert
SQL92 war nicht turing complete. Das ist lange her und mit CTEs und 
Window functions schon seit ein paar Jahren nicht mehr so.

von (Gast)


Bewertung
0 lesenswert
nicht lesenswert
sqlite ab 3.8.3 unterstützt eine "WITH" clause für rekursive queries.

http://sqlite.org/lang_with.html

Zum Datenmodell: ich würde eine spalte als parentid nach A bezeichnen 
und eine zweite spalte als parentid nach B, das macht die queries 
einfacher und lässt sich mit passender DB einfacher per referentieller 
integrität konsistent machen.

Beitrag #5338844 wurde vom Autor gelöscht.
von Uhu U. (uhu)


Bewertung
0 lesenswert
nicht lesenswert
SQL schrieb:
> select * from a left outer join p on a.parentid=p.id and a.parenttab=1
> order by a.id,a.parenttab,a.parentid

D.h., wenn ich alle A-Sätze bekommen will, die an P.2 hängen, müsste ich 
schreiben:

select * from a left outer join p on a.parentid=2 and a.parenttab=1
order by a.id,a.parenttab,a.parentid

Das habe ich in Sqliteman ausprobiert. Das Ergebnis ist leider leer.

von Uhu U. (uhu)


Bewertung
0 lesenswert
nicht lesenswert
rµ schrieb:
> Zum Datenmodell: ich würde eine spalte als parentid nach A bezeichnen
> und eine zweite spalte als parentid nach B, das macht die queries
> einfacher und lässt sich mit passender DB einfacher per referentieller
> integrität konsistent machen.

Stimmt, das würde das Modell einfacher machen und auch nicht mehr 
Speicher fressen.

von Boschi (Gast)


Bewertung
0 lesenswert
nicht lesenswert
SQL schrieb:
> select * from a left outer join p on a.parentid=p.id and a.parenttab=1
> order by a.id,a.parenttab,a.parentid

Ein einfaches Join reicht nicht, weil es 2 parent tables gibt, und beide 
müssen gejoint werden.
create table a(id int, pid int, ptable int, txt nvarchar(8))
go
create table p(id int, txt nvarchar(8))
go
insert into p (id, txt) values (0,'p0')
insert into p (id, txt) values (1,'p1')
insert into p (id, txt) values (2,'p2')
insert into p (id, txt) values (3,'p3')
go
insert into a (id, pid, ptable, txt) values (0,0,1,'a0') -- a0 - p0
insert into a (id, pid, ptable, txt) values (1,0,0,'a1') -- a1 - a0
insert into a (id, pid, ptable, txt) values (2,1,1,'a2') -- a2 - p1
insert into a (id, pid, ptable, txt) values (3,1,0,'a3') -- a3 - a1
insert into a (id, pid, ptable, txt) values (4,2,1,'a4') -- a4 - p2
insert into a (id, pid, ptable, txt) values (5,4,0,'a5') -- a5 - a4
insert into a (id, pid, ptable, txt) values (6,4,0,'a6') -- a6 - a4
go
select *,
  ca.txt as child, 
  case when ca.ptable=0 then pa.txt else pp.txt end as parent 
from a as ca  -- childA
  left outer join a as pa on ca.pid=pa.id  -- parentA
  left outer join p as pp on ca.pid = pp.id -- parentP
order by ca.id

Output:
id  pid  ptable  txt  id  pid  ptable  txt  id  txt  child  parent
0  0  1  a0  0  0  1  a0  0  p0  a0  p0
1  0  0  a1  0  0  1  a0  0  p0  a1  a0
2  1  1  a2  1  0  0  a1  1  p1  a2  p1
3  1  0  a3  1  0  0  a1  1  p1  a3  a1
4  2  1  a4  2  1  1  a2  2  p2  a4  p2
5  4  0  a5  4  2  1  a4  NULL  NULL  a5  a4
6  4  0  a6  4  2  1  a4  NULL  NULL  a6  a4

Irgendeinen Sonderfall habe ich vermutlich nicht berücksichtigt, sollte 
sich aber auch so abbilden lassen.

von Uhu U. (uhu)


Bewertung
0 lesenswert
nicht lesenswert

von Frank L. (Firma: Flk Consulting UG) (flk)


Bewertung
0 lesenswert
nicht lesenswert
Hallo Zusammen,

ich habe mir das Spiel nochmals angesehen. Die folgende Abfrage erzeugt 
korrekte Ergebnisse bei unveränderter Struktur.
select a1.* from p, a as a1 where p._id = %p and a1.ParentId = p._id and a1.ParentTab = 0 
union 
select a2.* from a as a2, a as a3 where a2._id = a3._id and a3.ParentTab = 1 and a2.ParentId in (select a1._id from p, a as a1 where p._id = %p and a1.ParentId = p._id and a1.ParentTab = 0)

Bitte auf das "%p" achten, dass muss an beiden Stellen mit der korrekten 
_id aus P gleich belegt werden.

Gruß
Frank

Antwort schreiben

Die Angabe einer E-Mail-Adresse ist freiwillig. Wenn Sie automatisch per E-Mail über Antworten auf Ihren Beitrag informiert werden möchten, melden Sie sich bitte an.

Wichtige Regeln - erst lesen, dann posten!

  • Groß- und Kleinschreibung verwenden
  • Längeren Sourcecode nicht im Text einfügen, sondern als Dateianhang

Formatierung (mehr Informationen...)

  • [c]C-Code[/c]
  • [avrasm]AVR-Assembler-Code[/avrasm]
  • [code]Code in anderen Sprachen, ASCII-Zeichnungen[/code]
  • [math]Formel in LaTeX-Syntax[/math]
  • [[Titel]] - Link zu Artikel
  • Verweis auf anderen Beitrag einfügen: Rechtsklick auf Beitragstitel,
    "Adresse kopieren", und in den Text einfügen




Bild automatisch verkleinern, falls nötig
Bitte das JPG-Format nur für Fotos und Scans verwenden!
Zeichnungen und Screenshots im PNG- oder
GIF-Format hochladen. Siehe Bildformate.
Hinweis: der ursprüngliche Beitrag ist mehr als 6 Monate alt.
Bitte hier nur auf die ursprüngliche Frage antworten,
für neue Fragen einen neuen Beitrag erstellen.

Mit dem Abschicken bestätigst du, die Nutzungsbedingungen anzuerkennen.