Bekomme Inner Joins nicht hin

[Der schöne Günther]

Eins vorweg: Ich habe mit Datenbanken extrem wenig Erfahrung.

Ich habe in meiner Datenbank "Items". Wenn diese verändert werden soll der alte Stand nicht gelöscht werden, sondern ein neuer hinzugefügt werden wo die Revisionsnummer höher ist.

Meine Struktur sieht so aus:

Item:

Name	Typ	Anmerkung
id	TEXT
rev	INT	Fkey auf Item_Rev.id
Daten1	?
Daten2	?

Item_Rev:

Name	Typ	Anmerkung
id	INT	(AutoInc)
changeTimestamp	DATETIME
changeComment	TEXT

Dann habe ich das auf meinem PC in einer Sqlite-Datenbank umgesetzt. Folgende Query sollte mir alle Items in der neusten Revision liefern:

Code:

SELECT
      id, Daten1, Daten2, MAX(changeTimestamp) changeTimestamp, changeComment
FROM Item a INNER JOIN (
       SELECT id fkey, MAX(changeTimestamp) changeTimestamp, changeComment
       FROM Item_Rev       
       GROUP BY fkey, changeComment
 ) b ON (a.rev = b.fkey)
 GROUP BY id

Gesagt, getan. In SQLite kommt genau heraus was ich mir vorgestellt habe 😎👍


Dann habe ich das auf MS-SQL umgesetzt. Die Query so nimmt er nicht an, er meint

Zitat:

SQL-Fehler [207] [S0001]: Ungültiger Spaltenname 'fkey'.

. Gut, dann habe ich das GROUP BY folgendermaßen abgeändert:

Code:

SELECT
      id, Daten1, Daten2, MAX(changeTimestamp) changeTimestamp, changeComment
FROM Item a INNER JOIN (
       SELECT id AS fkey, MAX(changeTimestamp) changeTimestamp, changeComment
       FROM Item_Rev       
       GROUP BY id, changeComment
 ) b ON (a.rev = b.fkey)
 GROUP BY id

Daraufhin meint er

Zitat:

SQL-Fehler [8120] [S0001]: Die Item.Daten1-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.

Gut, dann füge ich die auch noch an:

Code:

SELECT
      id, Daten1, Daten2, MAX(changeTimestamp) changeTimestamp, changeComment
FROM Item a INNER JOIN (
       SELECT id AS fkey, MAX(changeTimestamp) changeTimestamp, changeComment
       FROM Item_Rev       
       GROUP BY id, changeComment
 ) b ON (a.rev = b.fkey)
 GROUP BY id, Daten1, Daten2, changeComment

Das Problem: Ich erhalte hier die Items in allen ihren Revisionen. Es ist das gleiche als hätte ich geschrieben

Code:

SELECT [Spalten...] From Item JOIN Item_Rev ON (Item.rev = Item_Rev.id)

Was läuft hier falsch? Bin ich bei der "fkey"-Sache schon falsch abgebogen? Wenn SQLite etwas kann, dann muss MS-SQL das doch schon lange können, oder?

[Uwe Raabe]

So ganz steige ich da noch nicht durch. Das Inner-Select lautet

Code:

SELECT id AS fkey, MAX(changeTimestamp) changeTimestamp, changeComment
FROM Item_Rev      
GROUP BY id, changeComment

Dabei ist id laut Anmerkung ein AutoInc-Feld - also eindeutig. Ein Group By über ein eindeutiges AutoInc-Feld ist aber irgendwie witzlos, da jeder Feldwert ja eh nur einmal vorkommen kann. Du kannst ja mal einfach nur diese Query ausführen und wirst vermutlich alle Datensätze zurückbekommen.

[Der schöne Günther]

Ja, ich bekomme praktisch alles. Beide Tabellen haben eine Spalte id . Ich möchte natürlich nicht nach dem AutoInc-id, sondern nach der Text-ID aus Tabelle "item" gruppieren.

SQLite scheint das richtig zu machen. Wenn ich in MS-SQL jetzt aus "GROUP BY id" ein "GROUP BY Item.id" mache sagt er mir

Zitat:

SQL-Fehler [4104] [S0001]: Der mehrteilige Bezeichner 'Item.id' konnte nicht gebunden werden.

[mkinzler]

GROUP BY a.id ?

[Der schöne Günther]

Nope, ob mit a. oder ohne macht keinen Unterschied.

Ich verstehe das nicht. War der Sinn von SQL nicht dass man es über verschiedene Datenbank-Systeme hinweg verwenden könne?

[ibp]

Code:

SELECT
      id, Daten1, Daten2, MAX(changeTimestamp) changeTimestamp, changeComment
FROM Item a INNER JOIN (
       SELECT id AS fkey, MAX(changeTimestamp) changeTimestamp, changeComment
       FROM Item_Rev      
       GROUP BY id, changeComment
 ) b ON (a.rev = b.fkey)
 GROUP BY id, Daten1, Daten2, changeComment

vielleicht so:

Code:

SELECT
      a.rev, a.Daten1, a.Daten2, MAX(b.changeTimestamp) changeTimestamp, b.changeComment
FROM Item a 
INNER JOIN Item_Rev b ON (a.rev = b.id)
GROUP BY a.Daten1, a.Daten2, b.changeComment

[Delphi.Narium]

Höchste Revisionsnummer zum Item suchen: SELECT id, Max(rev) from Item group by ID

Das folgende führt nicht zum gewünschten Ergebnis, da hier das höchste Änderungsdatum zu ID UND Änderungskommentar gesucht wird. Das kann nur dann zum "annähernd" gewünschten Ergebnis führen, wenn alle Kommentare gleich sind.

SQL-Code:

SELECT id AS fkey, MAX(changeTimestamp) changeTimestamp, changeComment
FROM Item_Rev      
GROUP BY id, changeComment

Eventuell so?

SQL-Code:

select 
  Item.Id, 
  Item.Rev, 
  Item.Daten1, 
  Item.Daten2, 
  Item_Rev.changeTimestamp, 
  Item_Rev.changeComment 
from 
  Item, 
  Item_Rev,
  (SELECT Id, Max(Rev) As Rev from Item group by ID) Max_Rev
where Item.rev = Item_Rev.id
and Item.ID = Max_Rev.ID
and Item.Rev = Max_Rev.Rev

PS: Bin eher ein alter Knacker, deshalb nehme ich lieber die (für mich) leichter lesbare alte Form für Joins.

[Der schöne Günther]

Zitat von ibp:

vielleicht so

Danke, aber dann sagt er

Zitat:

SQL-Fehler [8120] [S0001]: Die Item.rev-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.

MS-SQL ist schon seltsam. Ich denke es jetzt mit einer völlig anders formulierten Query hinbekommen zu haben:

Code:

SELECT * FROM Auftrag JOIN Auftrag_Rev
ON (Auftrag.rev = Auftrag_Rev.id)
WHERE Auftrag.rev in (
   SELECT MAX(rev) rev FROM Auftrag JOIN Auftrag_Rev ON (Auftrag.rev = Auftrag_Rev.id)
   GROUP BY Auftrag.id
   )
;

In SQLite funktioniert das umgekehrt auch. Ist das jetzt geschickt oder eine Performance-Katastrophe? Und was MS-SQL eigentlich für ein Problem mit dem ersten Statement das in SQLite so gut geht?

Irgendwie weiß ich weshalb ich Kontakt mit relationalen Datenbanken so gut wie möglich vermeide...


Update

Zitat von Delphi.Narium:

PS: Bin eher ein alter Knacker, deshalb nehme ich lieber die (für mich) leichter lesbare alte Form für Joins.

Danke, das scheint auch zu gehen. Und ist wirklich sehr gut lesbar! Und geht auch in SQLite.

Vielen Dank!

[Uwe Raabe]

Zitat von Der schöne Günther:

War der Sinn von SQL nicht dass man es über verschiedene Datenbank-Systeme hinweg verwenden könne?

Nein, denn das S in SQL steht nicht für Standard sondern für Structured. Insbesondere gilt für Interoperability and standardization:

Zitat:

SQL implementations are incompatible between vendors and do not necessarily completely follow standards.

Es gibt zwar SQL Standards, aber die definieren in der Regel nur einen kleinsten gemeinsamen Nenner. Es ist mitunter auch gar nicht so leicht zu erkennen, wann man einen solchen Standard verlässt, wenn das aktuelle Konstrukt in der gerade verwendeten Datenbank zufällig noch funktioniert.

[Der schöne Günther]

Danke. Dann fühle ich in meiner Einstellung Bleibe Datenbanken fern solange du kannst ja bestärkt 🤣