SQL 2 Tabellen & Unterabfrage

[Rain]

Hallo,

folgendes Problem:
Ich hab eine Tabelle `buchungen` versehen mit ID und den Infos. Dann gibt es eine 2. Tabelle `buchungen_detail`, wo die ID wieder auftaucht und zu der ID es 1 bis X Einzelpositionen (Nr) gibt. In buchungen sind also die Kopfinfos und in buchungen_details die eigentlichen Buchungen.
Nun gibt es in der `buchung_details` noch einen Status, der 0,1 oder 2 annehmen kann (= Bearbeitsungsstatus). Nun benötige ich 2 Abfragen, wovon eine schon funktioniert.

Es sollen:
a) alle ID's angezeigt werden, bei denen in den Einzelpositionen der Status 0 vorkommt und
b) alle ID's angezeigt werden, bei denen in den Einzelpositionen der Status 0 NICHT vorkommt

(zum Verständnis: 0 = unbearbeitet)

a) funktioniert mit der Abfrage:

SELECT * FROM `buchungsanfragen` WHERE `ID` = ANY (SELECT `ID` FROM `buchungsanfragen_detail` WHERE `Status` = '0') ORDER BY `ID` desc;

sobald eine 0 gefunden wird, gibt er die ID aus. Super. Für b) kann ich aber leider nicht sagen WHERE `Status` <> '0', hier gibt er IDs zurück, wo durchaus eine 0 vorkommt, zB bei ID 11, da Nr 1-3 eine 1 haben, aber 4 eine 0.
Ist mein Problem irgendwie sql'ig lösbar?

 

[Rossibaer]

Zu a)

SELECT * FROM buchungsanfragen WHERE [B]ID IN[/B] (SELECT ID FROM buchungsanfragen_detail WHERE Status = '0') ORDER BY ID desc

Zu b)

SELECT * FROM buchungsanfragen WHERE [B]NOT ID IN[/B] (SELECT ID FROM buchungsanfragen_detail WHERE Status = '0') ORDER BY ID desc

Wenn es sich um Standard SQL handelt. Welches DBMS verwendest du? MySQL, Oracle, SQLServer, Informix?

 

[Rain]

MySQL verwende ich. Vielen Dank für deine Hilfe, es funktioniert genau so wie soll

 

[Mojo1987]

Sind die beiden Tabellen korrekt in Beziehung? Dann macht man das so aber nicht, weil das vorallem für große Tabellen unvorteilhaft ist.

Um sauber zu Arbeiten nutzt man eigentlich ein JOIN...

 

[Rossibaer]

Einen JOIN verwendet man, wenn man die Informationen aus beiden Tabellen selektieren will, nicht aber wenn es darum geht, was Rain eigentlich haben will, sprich die Master-Datensätze. Bei einem JOIN würde jeder Masterdatensatz entsprechend der Detaildatensätze wiederholt. Zudem müsste man dann die Spaltenauflistung auf die Spalten der Masterdatensätze reduzieren und kann nicht einfach nur mit dem "*" arbeiten. Das DBMS würde bei einem JOIN jede Masterzeile mit jeder Detailzeile entsprechend der JOIN Klausel verknüpfen um ein neues Resultset zu bilden, das dann entsprechend der WHERE Bedingung gefiltert wird. Aber alles in allem ist es wurscht, weil z.B. der SQLServer Optimizer aus den Subselects einer Where-Bedingung u.U. intern einen JOIN der Key-Spalten durchführt um zu filtern. Also wirst du bei einem richtigen JOIN nicht viel gewinnen, außer der Tatsache das dann mehr Zeilen angezeigt werden als du eigentlich haben willst!

Die Alternative zum "IN" wäre das "EXISTS" für die Subselects. Aber ob das performanter ist, hängt vom DBMS ab und ja es gibt da Unterschiede.

Der Vollständigkeit halber hier die EXISTS Variante:

SELECT * FROM buchungsanfragen WHERE EXISTS(SELECT * FROM buchungsanfragen_detail WHERE ID = buchungsanfragen.ID AND Status = '0') ORDER BY ID desc;
SELECT * FROM buchungsanfragen WHERE NOT EXISTS(SELECT * FROM buchungsanfragen_detail WHERE ID = buchungsanfragen.ID AND Status = '0') ORDER BY ID desc;

 

[Rain]

die tabellen sind in korrekter beziehung hoffe ich:

buchungsanfragen hat als primärschlüssel die ID, buchungsanfragen_detail hat als primärschlüssel auch die ID und die PositionsNr.

Hab ganz vergessen, dass ich die Tabellen ja als Bild noch mit anhängen wollte. Ich hab hier zwar ne Menge vor mir rumliegen über JOIN und hab ein bissel damit rumgespielt, aber so richtig schlau werde ich daraus irgendwie nicht

SELECT ID FROM buchungsanfragen JOIN buchungsanfragen_detail ON (Status = '0')

funktioniert natürlich nicht ..

 

[Rossibaer]

Den JOIN würdest du eher so schreiben:

SELECT * 
FROM buchungsanfragen 
JOIN buchungsanfragen_detail ON buchungsanfragen.ID=buchungsanfragen_detail.ID
WHERE buchungsanfragen_detail.Status = '0'

Das würde aber wie oben schon beschrieben für jede Detailzeile mit Status ='0' eine Zeile mit allen Spalten beider Tabellen ausgeben. Ist halt die Frage ob du das willst oder nicht.

Wenn du buchungsanfragen_detail richtig in eine Beziehung mit buchungsanfragen setzen willst, dann musst du bei buchungsanfragen_detail einen ForeignKey für die ID Spalte setzen. Außerdem empfiehlt es sich noch einen Index auf die ForeignKey Spalte zu legen, damit der Zugriff auch performant erfolgen kann.

 

[Rain]

also, respekt für dein knowhow! mit sql beschäftigst du dich nicht erst seit nem halben jahr nehme ich an.

die kompletten spalten beider tabellen brauche ich tatsächlich nicht jedes mal, deswegen bleibe ich erst mal deiner ersten variante.

vielen dank nochmal!

 

[Magic1416]

Wieso den String nicht um Distinct erweitern. Damit fallen die doppelten Einträge raus, wenn man nur die Ergebnisse aus der ersten Table braucht. Meiner Meinung nach die einfachste Lösung

SELECT Distinct * 
FROM buchungsanfragen 
JOIN buchungsanfragen_detail ON buchungsanfragen.ID=buchungsanfragen_detail.ID
WHERE buchungsanfragen_detail.Status = '0'

Gruss Magic

 

[Rossibaer]

Ist interessant, wird aber nicht funktionieren, da durch den Stern alle Spalten beider Tabellen vom Distinct berücksichtigt werden.

Gruß Rossibaer

 

[rIQ]

Pflichtlektüre für jeden, der eine Datenbank quälen will

http://en.wikipedia.org/wiki/Join_(SQL)

Ergänzung (15. Januar 2010)

Den JON würdest du eher so schreiben:

SELECT * 
FROM buchungsanfragen 
JOIN buchungsanfragen_detail ON buchungsanfragen.ID=buchungsanfragen_detail.ID
WHERE buchungsanfragen_detail.Status = '0'

Das würde aber wie oben schon beschrieben für jede Detailzeile mit Status ='0' eine Zeile mit allen Spalten beider Tabellen ausgeben. Ist halt die Frage ob du das willst oder nicht.

Wenn du buchungsanfragen_detail richtig in eine Beziehung mit buchungsanfragen setzen willst, dann musst du bei buchungsanfragen_detail einen ForeignKey für die ID Spalte setzen. Außerdem empfiehlt es sich noch einen Index auf die ForeignKey Spalte zu legen, damit der Zugriff auch performant erfolgen kann.

[FONT=Courier New][SIZE=4]SELECT b1.ID, b1.Infos 
FROM buchungsanfragen AS b1
JOIN buchungsanfragen_detail AS d1 
ON b1.ID=d1.ID

WHERE d1.Status = '0'[/SIZE][/FONT]

bzw.

[FONT=Courier New][SIZE=4]WHERE d1.Status <> '0'[/SIZE][/FONT]

 

[Rossibaer]

Oder (wenn es nur um die id und infos aus buchungsanfragen geht) ...

SELECT [B]DISTINCT[/B] b1.ID, b1.Infos 
FROM buchungsanfragen AS b1
JOIN buchungsanfragen_detail AS d1 
ON b1.ID=d1.ID
WHERE d1.Status = '0'

 

[Magic1416]

Ja Rossibaer. So wie der Query zuletzt von Dir so meinte ich es eigentlich auch.

Gruss Magic

 

[Rossibaer]

@Magic: Ok, dann hab ich das jetzt eigentlich auch verstanden. War mir halt unsicher, wie es gemeint war, da ja der * als Platzhalter für alle Spalten steht und somit ein DISTINCT nicht zum Ziel führt.

Happy coding...