Verknüpfte Einträgen die sich beim Datum überschneiden ermitteln

[WilderStorch]

Hallo, ich habe ein Problem, welches ich per SQL-Befehl lösen muss.
Mein Datenbankschema sieht folgendermaßen aus:

CREATE TABLE TABLE_A
(
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
)

CREATE TABLE TABLE_B
(
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    TABLE_A_ID INT NOT NULL FOREIGN KEY REFERENCES TABLE_A(ID),
    BEGINN DATETIME NOT NULL,
    ENDE DATETIME NOT NULL
)

Ich habe eine SQL-Befehl welcher alle Einträge der Tabelle "TABLE_A" ausgibt. Hier muss ich jetzt pro Eintrag der Tabelle ermitteln, ob es Einträge in der Tabelle "TABLE_B" gibt, welche sich vom Zeitraum überschneiden.

Ich schaffe es nur alle Einträge der Tabelle "TABLE_B" zu Zählen, die mit dem jeweiligen Datensatz der Tabelle "TABLE_A" verknüpft sind:

SELECT
    TABLE_A.ID,
    (SELECT COUNT(TABLE_B.ID) FROM TABLE_B WHERE TABLE_B.TABLE_A_ID = TABLE_A.ID) AS ANZAHL_EINTRAEGE,
FROM
    TABLE_A;

Ist das so überhaupt möglich? Mein Problem ist, dass meine Voraussetzung sind, dass ich das ganze in einem SQL-Befehl abfragen muss.

 

[akretschmer]

select A.ID, count(b.*) from table_a a left join table_b b on a.id=b.table_a_id group by a.id;

 

[WilderStorch]

Danke für die schnelle Antwort, aber ich glaube mein Problem wurde missverstanden.

select A.ID, count(b.*) from table_a a left join table_b b on a.id=b.table_a_id group by a.id;

Dieser Befehl zählt alle Einträge der Tabelle "TABLE_B" die zum jeweiligen Eintrag der Tabelle "TABLE_A" gehören. Ich möchte aber wissen ob es Einträge der Tabelle "TABLE_B" zum jeweiligen Datensatz der Tabelle "TABLE_A" gibt, die sich vom Zeitraum (TABLE_B.BEGINN, TABLE_B.ENDE) her überschneiden.

 

[akretschmer]

Deine Tabelle A hat ja keine weiteren Datensätze...

Ach so, Du willst generell wissen, ob es bei gleichem table_a_id in der B-Tabelle sich überlappende Zeiten gibt? Das wäre dann wohl nicht gut, wie z.B. bei einem Hotel (die ID als Zimmernummer) und dann die Buchungszeiten?

Falls Du das willst, wäre es besser, dies mit einem Constraint gleich zu verhindern...

 

[WilderStorch]

Genau das möchte ich
Einen solchen Constraint gibt es auch bereits, nur leider handelt es sich hier um Daten die aus einem Fremdverfahren konvertiert worden sind. Für die Konvertierung wurden die Constraints deaktiviert, damit solche Datensätze die eigentlich "keinen Sinn ergeben" auch übernommen werden. Eben diese Fälle muss ich jetzt ermitteln.

 

[akretschmer]

test=*# select * from tab_b;
 id |  von  |  bis   
----+------------+------------
  1 | 2018-01-01 | 2018-01-10
  1 | 2018-01-12 | 2018-01-20
  2 | 2018-01-05 | 2018-01-15
  2 | 2018-01-10 | 2018-01-20
(4 Zeilen)

test=*# select * from tab_b b1 cross join tab_b b2 where b1.id=b2.id and (b1.von,b1.bis) != (b2.von,b2.bis) and (b1.von,b1.bis) overlaps (b2.von, b2.bis);
 id |  von  |  bis  | id |  von  |  bis   
----+------------+------------+----+------------+------------
  2 | 2018-01-05 | 2018-01-15 |  2 | 2018-01-10 | 2018-01-20
  2 | 2018-01-10 | 2018-01-20 |  2 | 2018-01-05 | 2018-01-15
(2 Zeilen)

test=*#

Wie hast denn den Constraint definiert?

test=*# \d zimmer
  Tabelle »public.zimmer«
 Spalte |  Typ  | Sortierfolge | NULL erlaubt? | Vorgabewert
--------+---------+--------------+---------------+-------------
 id  | integer |  | not null  |
 name  | text  |  | not null  |
Indexe:
  "zimmer_pkey" PRIMARY KEY, btree (id)
Fremdschlüsselverweise von:
  TABLE "hotel" CONSTRAINT "hotel_zimmer_fkey" FOREIGN KEY (zimmer) REFERENCES zimmer(id)

test=*# \d hotel;
  Tabelle »public.hotel«
 Spalte  |  Typ  | Sortierfolge | NULL erlaubt? | Vorgabewert
---------+-----------+--------------+---------------+-------------
 zimmer  | integer  |  |  |
 gebucht | daterange |  |  |
Indexe:
  "hotel_zimmer_gebucht_excl" EXCLUDE USING gist (zimmer WITH =, gebucht WITH &&)
Fremdschlüssel-Constraints:
  "hotel_zimmer_fkey" FOREIGN KEY (zimmer) REFERENCES zimmer(id)

test=*# insert into zimmer values (1, 'Zimmer 1');
INSERT 0 1
test=*# insert into zimmer values (2, 'Zimmer 2');
INSERT 0 1
test=*# insert into hotel values (2, '[2018-01-05,2018-01-15)');
INSERT 0 1
test=*# insert into hotel values (2, '[2018-01-15,2018-01-25)');
INSERT 0 1
test=*# insert into hotel values (2, '[2018-01-20,2018-01-30)');
ERROR:  conflicting key value violates exclusion constraint "hotel_zimmer_gebucht_excl"
DETAIL:  Key (zimmer, gebucht)=(2, [2018-01-20,2018-01-30)) conflicts with existing key (zimmer, gebucht)=(2, [2018-01-15,2018-01-25)).
test=*#