LEFT JOIN findet validen Wert und NULL

[pi4444]

Hallo zusammen.

Ich habe ein seltsames Problem bei LEFT JOINS. Diese liefern bei mir für eine Zeile einmal den gewünschten Wert zurück und zusätzlich eine identische Zeile nur mit einem NULL Eintrag bei dem gewünschten Wert.
Ich habe die Folgenden Tabellen:

STATE (speichert einen State mit id und einem namen)
id | name
0 | c
1 | a
2 | b

AUTOMAT (speichert einen Automaten mit id und einem namen)
id | name
0 | foo

AUT_STATE (speichert die ids der Automaten und die dazugehörigen State ids, die in diesem liegen)
aut | state
0 | 1
0 | 2

VIEW_AUT_STATE (eine view die bis jetzt nicht viel macht, sondern nur states automaten zuordnet. Der Sinn sei hier mal nicht so wichtig)
id | aut | state
0 | 0 | 1
1 | 0 | 2


Nun habe ich ein Query das alle Elemente aus VIEW_AUT_STATE selektiert und noch eine neue Spalte einfügt für andere mögliche States die in dem Automaten liegen. Dabei soll die id von state und state2 nicht identisch sein. Und state2 soll den namen "b" haben

SELECT DISTINCT v.*, s.id AS state2
FROM VIEW_AUT_STATE AS v
LEFT JOIN AUT_STATE AS as ON as.aut = v.aut
LEFT JOIN STATE AS s ON v.state != s.id AND
as.aut = v.aut AND
as.state= s.id AND
s.name = "b";

Nun bekomme ich als Ergebnis:

id | aut | state | state2
0 | 0 | 1 | NULL
0 | 0 | 1 | 2
1 | 0 | 2 | NULL

Wie kann es sein, dass ich für die 1. Zeile in VIEW_AUT_STATE (0 | 0 | 1) zwei Einträge bekomme?
Einen mit dem Wert, den ich erwartet hätte (2) und einmal mit NULL.

Soweit ich LEFT JOINS verstanden habe, wird nur dann mit NULL aufgefüllt, wenn es für diese Spalte kein valides Element gibt. Hier jedoch ist die 2 valide und trotzdem bekomme ich die Zeile mit NULL.

Das Ergebnis was ich also erwartet hätte, ist:
id | aut | state | state2
0 | 0 | 1 | 2
1 | 0 | 2 | NULL

Hat jemand eine Idee was ich hier falsch mache?

 

[akretschmer]

dein view VIEW_AUT_STATE hat für aut = 0 zwei unterschiedliche States.

 

[pi4444]

@akretschmer
Danke für deine Antwort.
Das VIEW_AUT_STATE hier für aut=0 zwei unterschiedliche states hat, soll so sein. Ein AUTOMAT soll mehrere STATES referenzieren können.

Ich glaube ich verstehe nur noch nicht so ganz, warum dies dann zu dem oben gegebenem Ergebnis führt.
Könntest du mir das vielleicht noch etwas genauer erklären?

 

[akretschmer]

du kannst ja mal die join-conditions lockern:

test=*# select  view_aut_state.*, state.id as state2
from view_aut_state
LEFT JOIN AUT_STATE on AUT_STATE.aut = view_aut_state.aut
LEFT JOIN STATE on view_aut_state.state != STATE.id
-- and AUT_STATE.aut=view_aut_state.aut
-- and AUT_STATE.state = STATE.id
-- and STATE.name = 'b'
;
 id | aut | state | state2
----+-----+-------+--------
  0 |   0 |     1 |      0
  0 |   0 |     1 |      2
  0 |   0 |     1 |      0
  0 |   0 |     1 |      2
  1 |   0 |     2 |      0
  1 |   0 |     2 |      1
  1 |   0 |     2 |      0
  1 |   0 |     2 |      1
(8 rows)

test=*# select  view_aut_state.*, state.id as state2
from view_aut_state
LEFT JOIN AUT_STATE on AUT_STATE.aut = view_aut_state.aut
LEFT JOIN STATE on view_aut_state.state != STATE.id
and AUT_STATE.aut=view_aut_state.aut
-- and AUT_STATE.state = STATE.id
-- and STATE.name = 'b'
;
 id | aut | state | state2
----+-----+-------+--------
  0 |   0 |     1 |      0
  0 |   0 |     1 |      2s.name = "b";
  0 |   0 |     1 |      0
  0 |   0 |     1 |      2
  1 |   0 |     2 |      0
  1 |   0 |     2 |      1
  1 |   0 |     2 |      0
  1 |   0 |     2 |      1
(8 rows)

test=*# select  view_aut_state.*, state.id as state2
from view_aut_state
LEFT JOIN AUT_STATE on AUT_STATE.aut = view_aut_state.aut
LEFT JOIN STATE on view_aut_state.state != STATE.id
and AUT_STATE.aut=view_aut_state.aut
and AUT_STATE.state = STATE.id
-- and STATE.name = 'b'
;
 id | aut | state | state2
----+-----+-------+--------
  0 |   0 |     1 |       
  0 |   0 |     1 |      2
  1 |   0 |     2 |      1
  1 |   0 |     2 |       
(4 rows)

test=*# select  view_aut_state.*, state.id as state2
from view_aut_state
LEFT JOIN AUT_STATE on AUT_STATE.aut = view_aut_state.aut
LEFT JOIN STATE on view_aut_state.state != STATE.id
and AUT_STATE.aut=view_aut_state.aut
and AUT_STATE.state = STATE.id
and STATE.name = 'b'
;
 id | aut | state | state2
----+-----+-------+--------
  0 |   0 |     1 |       
  0 |   0 |     1 |      2
  1 |   0 |     2 |       
  1 |   0 |     2 |       
(4 rows)

test=*#

Du verwendest Konstrukte wie AS as und s.name = "b", das sind aber die falschen, korrekt wären '.


Hilft das weiter?

 

[pi4444]

@akretschmer
Danke, für das Aufteilen des Beispiels, ich denke ich habe das Problem nun besser verstanden.

Hast du vielleicht noch eine Idee, wie ich mein eigentlich gewolltes Ergebnis erreiche?
Also :
id | aut | state | state2
0 | 0 | 1 | 2
1 | 0 | 2 | NULL

Also ob es ein sinnvolleres Query gibt, das mir nur die Einträge zurück liefert mit Wert, falls es einen gibt oder NULL falls es keinen gibt. Ohne die zusätzlichen Zeilen?
Oder ist die Tabellen-Struktur einfach ungeeignet gewählt für diesen Use-Case?