Bekomme Inner Joins nicht hin

[jobo]

Hier sind 3 Varianten ohne Anspruch auf Vollständigkeit usw:

Code:

-- nur max(id) wenn garantiert(!) ist, dass sie eindeutig, aufsteigend ist (faule Lösung)
-- geht "immer" (Syntax)
select a.*, r.*
  from item a
  join (select max(id) fkey from Item_Rev) b
    on a.rev = b.fkey
  join item_rev r
    on a.rev = r.id;
  

-- erst max(timestamp), darüber die zugehörige ID holen-Reihenfolge nun egal-  ("ordentliche" Lösung) 
--   ordentlich halt*, Syntax geht auch immer 
select a.*, r.*
  from item a
  join (select id
          from item_rev ri
          join (select max(changetimestamp) x from Item_Rev) rx
            on ri.changetimestamp = rx.x) b
    on a.rev = b.id
  join item_rev r
    on a.rev = r.id;
  
-- top n, logisch sauber, vermeidet Group ganz, trendy
--   bin mir nicht ganz sicher, aber es wäre eine typische MS Lösung, die relativ früh (als erste?) die TOP Funktion eingeführt hatten
--   ich würde mal sagen, eine gute Lösung, m.E. ohne Group sozusagen straight forward, aber syntaktisch nicht sehr kompatibel 
--      (ersetze "top n" durch "limit n" o.ä. für andere Systeme)
select a.*, r.*
  from Item a
  join (select top 1 id from Item_Rev 
         order by changetimestamp desc) b
    on a.rev = b.id
  join item_rev r
    on a.rev = r.id;

zu *
Alles ist relativ. Diese Lösung vermeidet das vielleicht etwas theoretische ID Problem (aufsteigend=chronologische ID= höchste Revision).
Aber was ist, wenn changetimestamp sich später (per Trigger) ändert, weil z.B. rein Rechtschreibfehler korrigiert wurde?


Die anfangs von Dir gefundene SQLite Variante hat mich mehr als überrascht und stark an das mySQL Desaster erinnert.
Ich war bis gerade der Meinung, mySQL seien die Einzigen, die sowas gefährliches "anbieten". Deren Group By Syntax war angetreten, ~ohne weiteres~ eine richtige Antwort zu liefern, es kommt dort aber meist Schrott raus, wenn man sich nicht an die Spielregeln hält. SQLite ist dann wohl Nr. 2 in dieser Heldenliste.
Ein Zitat aus deren Hilfe

Zitat:

Side note: Bare columns in an aggregate queries. ...
... and so in many cases the value for "b" is undefined...

Das Ergebnis ist also undefiniert!!! Dein (richtiges) Ergebnis wäre nach dieser Beschreibung also Zufall?
Es geht noch weiter in der Hilfe. Dort wird beschrieben, dass nur bei min und max etwas besonderes gemacht wird, was dann tatsächlich Dein Ergebnis erklärt. Eine sehr bequeme "Abkürzung" für den Entwickler von etwas SQL Text, aber tatsächlich weit weg vom Standard. Ich find besonders den ersten Teil gefährlich. Würde ich mich nicht dran gewöhnen. Vielleicht hast Du selbst diese Doku gelesen und warst der Meinung, so gehört sich das ..

Und wo ich dabei bin. was das Thema "kompatibel" angeht:
Es gibt die verschiendenen ANSI SQL Versionen von 86 bis 2016. Daran hält sich leider niemand vollständig (Was m.E. schlimmer klingt, als es ist, da man bei sowas offenbar schon die wichtigsten Dinge angeht)
Den Wunsch nach Kompatibilität versteh ich gut, aber es ist wie bei verschiedenen Pascal Dialekten, eben nicht alles kompatibel.
SQLite ist m.E. ein Exot, weil es ganz klar anders positioniert ist, als MSSQL oder andere große. Auf der Ebene am ehesten vergleichbar mit dem embedded Part von Firebird, aber eben nur mit dem. Ebenso exotisch der lockere Umgang mit Typen, low concurrency Ansatz, ..
MSSQL ist da gefühlt eher Standard, aber fett und reich genug, eigene Features vor den Standard zu stellen. Das gilt ähnlich für die anderen Kommerzanbieter. Gewollt sind am ehesten die open source Systeme bemüht, Standards einzuhalten, hier fehlen dann leider oft die Ressourcen. Postgres legt allerdings sehr großen Wert auf die ANSI-SQL Normen und kriegt das auch ganz gut hin.
In der Praxis geschieht es häufig, dass man sich für eine Syntax entlang der Doku des verwendeten Systems entscheidet und dabei nicht Standard gemäß formuliert, obwohl die DB es verstehen würde. Die Mohrrübe ist dabei oft auch eine kleine Extraportion Komfort oder Funktionalität. "Alte Hasen" haben da den kleinen Vorteil, dass sie aus Gewohnheit "gut abgehangenes" SQL verwenden. (Was nicht immer wirklich ein Vorteil sein muss, weil sie die Mohrrübe gar nicht sehen).

Zum "Problem"
Für Group By gibt es eine Faustregel, mit der man gut fährt:
Alle nicht aggregierten Felder der Select Liste müssen im Group By genannt werden.

[mkinzler]

Zitat:

Auf der Ebene am ehesten vergleichbar mit dem embedded Part von Firebird, aber eben nur mit dem. Ebenso exotisch der lockere Umgang mit Typen, low concurrency Ansatz, ..

Wobei Firebird hier natürlich viel mehr Standardkonformität bietet.

[jobo]

Ja, ich meinte das Produkt an sich und seine "Nische" /Biotop. Der kleine Fußabruck und das Embedding schienen mir da bei Firebird ein gute Analogie.

[mkinzler]

Habe ich auch so verstanden. Ich finde nur das FB embedded die bessere Alternative darstellt.

[jobo]

Zitat von mkinzler:

Ich finde nur das FB embedded die bessere Alternative darstellt.

Das kann man wahrscheinlich hier so sagen, zumindest für einen Entwickler, der in der Produktivwelt mit richtigen RDBMS (MSSQL) arbeitet.
Bei Entwicklungen mit Fokus Android ist das vielleicht noch was anderes.

Aber auch wenn die Ähnlichkeiten groß sind (z.B. single file fällt mir grad noch ein), allein die mögliche Skalierung mit firebird embedded ist sehr viel wert.

OT oder vielleicht auch nicht:
Ich habe die Tage noch etwas über den Tellerrand geschaut und mal durch die top Stacks der nerdigen Platzhirsche geschaut. Was es da alles "krassen" Tools gibt. Bei manchen hab ich einfach mal youtube angeworfen und ein Einsteiger Tutorial durchgeschaut. Meine Empfindung dabei reicht von Unverständnis über "ganz ok" bis "Damit müsste man mal ein Projekt" machen.
Aber was mich schon gewundert hat: Dieses ganze -im weitesten Sinne- noSQL Gepfriemel und die erfreuten Ausraster, wenn es dann ein Tool mal wieder etwas besser macht. "Ich kann ganz gezielt Daten abfragen! ..Wie krass ist das denn?!"

Wie auch immer, ~"lass bloß die Finger von SQL" hier irgendwo vom TE hat mich ja dann gereizt, ein bisschen dazu zu schreiben. Ich sehe keine wirklichen Alternativen und wenn man sich ein wenig drauf einlässt, sehe ich auch eigentlich keine Probleme. Schon gar nicht für jemand der eine syntaktisch komplexe Programmiersprache wie C, C++, Pascal, .. nutzt.
Fairerweise muss man zugestehen, dass Systeme wie SQLite oder mySQL, die zufällige Ergebnisse liefern, nicht gerade eine Ermutigung sind, sich mit ihnen auseinanderzusetzen oder besser vertraut zu machen.

@MichaelT: Eine entspannte Sicht auf das Datenmodell und die passenden Werkzeuge gefällt mir, aber beim Thema Constraints kann ich nicht recht folgen. Es ist keine dumpfe Domainprüfung, es sind (komplexe) Regeln, zumindest kann es das sein. Und warum nicht nutzen, auch für FK, wenn ich es geschenkt bekomme und damit robust und konsistent
werde / bleibe.

[p80286]

Zitat von jobo:

Wie auch immer, ~"lass bloß die Finger von SQL" hier irgendwo vom TE hat mich ja dann gereizt, ein bisschen dazu zu schreiben. Ich sehe keine wirklichen Alternativen und wenn man sich ein wenig drauf einlässt, sehe ich auch eigentlich keine Probleme. Schon gar nicht für jemand der eine syntaktisch komplexe Programmiersprache wie C, C++, Pascal, .. nutzt.

Es gibt einen kleinen Unterschied, (SQL-)Datenbanken verlangen eine "Mengendenke" und für viele Programmierer ist es oft unverständlich, daß das Ergebnis eines Select... immer eine zufällige Reihenfolge besitzt.

Gruß
k-H

[jobo]

Mengenlehre ja! Das hatte ich damals schon in der Grundschule.
Keine Ahnung was die geraucht hatten, vielleicht war das ein geheimes Forschungsprojekt, an dem nur unsere Schule in diesem Kuhkaff teilgenommen hat. Vielleicht kann man nun auch anhand der Lehrpläne für NRW mein Geburtsjahr rekonstruieren, denn das gab's bestimmt nur einmal.
Hab es damals nicht so toll gefunden.

Vielleicht hast Du Recht, das Mengenthema und die Vermittlung scheint problematisch zu sein. Aber hier war es glaub ich nicht das Hauptthema.
Datenmodell und das beste, kompatible Select-Statement ist noch was anderes oder?
Ich mein, niemand hätte sich das sqlite-Group-by-Statement "ausgedacht", das DSG gepostet hat, wenn er nicht die Doku gelesen hätte. Und diese "Kniffe", bei der Verabeitung der speziellen Besonderheiten bei einem Group By Statement in SQLite mit Min oder Max, hat das was mit Mengenlehre zu tun? Das ist eher Brainfuck ala Nerdtool, wie ich sie erwähnt hab.
Oder eher, wenn nicht bald Weihnachten wäre (und es nichtoffiziell in der SQLite Doku stünde), würde ich sagen, DSG hat ein Easteregg entdeckt.