Ist so eine SQL-Abrage mit einem SQL-Befehl möglich?

[Uhu U. (uhu)]

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.

[T.roll (Gast)]

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.

[Frank L. (Firma: Flk Consulting UG) (flk)]

Hallo
Ja geht, such mal nach nested select bei Tante Google

Gruß
Frank

[Sheeva P. (sheevaplug)]

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

[Uhu U. (uhu)]

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...

[Frank L. (Firma: Flk Consulting UG) (flk)]

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

[Uhu U. (uhu)]

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.

[Frank L. (Firma: Flk Consulting UG) (flk)]

Hey
Ich schaue es mir morgen Vormittag mal
Gruß
Frank

[Uhu U. (uhu)]

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?

[Jim M. (turboj)]

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.

[Frank L. (Firma: Flk Consulting UG) (flk)]

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

[Frank L. (Firma: Flk Consulting UG) (flk)]

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

[Frank L. (Firma: Flk Consulting UG) (flk)]

Sorry meinte ParentId

[Uhu U. (uhu)]

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.

[Frank L. (Firma: Flk Consulting UG) (flk)]

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

[Frank L. (Firma: Flk Consulting UG) (flk)]

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

[Uhu U. (uhu)]

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.)

[Frank L. (Firma: Flk Consulting UG) (flk)]

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

Gruß
Frank

[Uhu U. (uhu)]

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.

[SQL (Gast)]

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

[Ergo70 (Gast)]

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

[rµ (Gast)]

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.

[Uhu U. (uhu)]

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.

[Uhu U. (uhu)]

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.

[Boschi (Gast)]

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.

[Uhu U. (uhu)]

Was spricht gegen meine Lösung aus 
Beitrag "Re: Ist so eine SQL-Abrage mit einem SQL-Befehl möglich?" ?

[Frank L. (Firma: Flk Consulting UG) (flk)]

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