Bedingte Abfrage

[samic]

Guten Morgen!

Ich kämpfe schon länger mit folgender Abfrage:
Ich habe eine Tabelle mit Auftragen (ord) und eine mit den dazugehörigen Positionen (position). Die Aufträge müssen den Status 'o1' haben und die Positionen können den Status p1 bis p18 haben, dürfen aber nur Status p15 bis p18 haben.

Die Abfrage soll jetzt nur jene Auftragsnummer liefern, die in den Positionen NUR p15 bis p18 haben, doch leider funzt das hier nicht:

SELECT DISTINCT ord.order_id FROM ord LEFT JOIN position ON ord.order_id=position.order_id
WHERE ord.status='o1'
AND ( position.status='p15' OR position.status='p16' OR position.status='p17' OR position.status='p18')
AND (position.status!='p1' OR position.status!='p2' OR position.status!='p3'
OR position.status!='p4' OR position.status!='p5'
OR position.status!='p6' OR position.status!='p7'
OR position.status!='p8' OR position.status!='p9'
OR position.status!='p10' OR position.status!='p11' OR
position.status!='p12' OR position.status!='p14')


Weiss jemand Rat? Vielen Dank für eure Hilfe!

[PHP-Boernie]

Gibt es zu einem Auftrag mehrere Statussätze? Also sind die Tabellen ord und position 1:n?

SELECT DISTINCT ord.order_id FROM ord LEFT JOIN position ON ord.order_id=position.order_id
WHERE ord.status='o1'
AND ( position.status='p15' OR position.status='p16' OR position.status='p17' OR position.status='p18')

Ich versteh nicht ganz wozu der andere kram noch sein soll? Du machst doch hier alles schon eindeutig oder willst du manchmal auch die kleineren also p1 - p14 haben?

noch was:

was kommt denn jetzt zurück und was wäre dein erwartetes ergebnis?

[samic]

Ja genau, sorry das habe ich vergessen zu schreiben!

Ein Auftrag kann mehrere Positionen haben und eine Position kann mehrere Status haben.

[samic]

SELECT DISTINCT ord.order_id FROM ord LEFT JOIN position ON ord.order_id=position.order_id
WHERE ord.status='o1'
AND ( position.status='p15' OR position.status='p16' OR position.status='p17' OR position.status='p18')

liefert zwar jetzt alle Aufträge die EINE Positionen im Status p15 bis p18 haben, aber es gibt dabei trotzdem noch Positionen zu den Aufträgen die Status p2 oder p3, etc. haben!

[PHP-Boernie]

meinst du wirklich mehrere Status zu einer Position so dass folgendes entsteht:

ordosition:3. tabelle = 1:n:n ???

oder meinst du nur verschiedene status?

und jetzt bitte nur noch das ergebnis und das erwartete ergebnis

[samic]

Mist, falsch ausgedrückt. Also nochmals:

Ein Autrag hat ein Status (o1 bis o6) und hat mehrere Positionen, eine Position hat ein Status (p1 bis p18)

Und ich möchte nur die Aufträge, bei denen die Positionen den Status p15-p18 haben und nicht auch noch p1-p14

[PHP-Boernie]

Sag das doch ;-)

SELECT DISTINCT ord.order_id FROM ord LEFT JOIN position ON ord.order_id=position.order_id
WHERE ord.status='o1'
AND ( position.status='p15' OR position.status='p16' OR position.status='p17' OR position.status='p18')

Füge noch dieses hinzu:

AND position.position_id NOT IN (SELECT position_id FROM position WHERE status IN ('p1', 'p2', 'p3', 'p4', 'p5', 'p6', 'p7', 'p8', 'p9', 'p10', 'p11', 'p12', 'p13', 'p14') AND position.order_id = ord.order_id)

Allerdings brauchen deine Positionen dafür eine eindeutige laufende Nummer also ein Feld mit auto increment oder eines wo du das auto increment selber machst.

So sollte es funktionieren.

Achja funktioniert glaub ich nur bei MySQL 4 und höher

[samic]

wow, danke für die schnelle Reaktion :-)

SELECT DISTINCT ord.order_id FROM ord LEFT JOIN position ON ord.order_id=position.order_id
WHERE ord.status='o1'
AND ( position.status='p15' OR position.status='p16' OR position.status='p17' OR position.status='p18')
AND position.pos_id NOT IN (SELECT pos_id FROM position
WHERE status IN ('p1', 'p2', 'p3', 'p4', 'p5', 'p6', 'p7', 'p8', 'p9', 'p10', 'p11', 'p12', 'p13', 'p14')
AND position.order_id = ord.order_id) order by order_id

Funktioniert leider immer noch nicht. Das mit der ID versteh ich nicht ganz. Wozu braucht man das?
pos_id ist ein autoincrement und primary key und mysql ist 4.x

[PHP-Boernie]

Das brauchst du für den Inline Select nennt man das.

Hiermit:

SELECT pos_id FROM position
WHERE status IN ('p1', 'p2', 'p3', 'p4', 'p5', 'p6', 'p7', 'p8', 'p9', 'p10', 'p11', 'p12', 'p13', 'p14')
AND position.order_id = ord.order_id

holst du dir ja eine Menge an Positionen die den Status p1-p14 haben und die gleiche OrderID wie aus dieser Menge:

SELECT DISTINCT ord.order_id FROM ord LEFT JOIN position ON ord.order_id=position.order_id
WHERE ord.status='o1'
AND ( position.status='p15' OR position.status='p16' OR position.status='p17' OR position.status='p18')

Nur darf die erste Menge nicht in der zweiten vorkommen durch das NOT IN.

Aber ich glaub ich weiß wo dein Problem liegt. Nun kommt bestimmt zu wenig oder?

Kann es sein dass du die Menge haben willst die jemals einen Status p15-p18 bekommen hat aber noch nie einen Status p1-p14 bekommen hat?

[samic]

Kann es sein dass du die Menge haben willst die jemals einen Status p15-p18 bekommen hat aber noch nie einen Status p1-p14 bekommen hat?

Genau, also zum Bespiel sieht das so aus:

Auftrag1, o1
Position1, Auftrag1, p7
Position2, Autrag1, p15
Position3, Autrag1, p16

Auftrag2, o1
Position1, Auftrag2, p16
Position2, Auftrag2, P17

Jetzt soll die Abfrage nur Auftrag2 liefern und nicht Auftrag1, es muss ein "reiner" Auftrag mit den Status p15 bis p18 in den Positionen sein.

[PHP-Boernie]

Mir ist da ein kleiner Fehler unterlaufen:

SELECT DISTINCT ord.order_id FROM ord LEFT JOIN position ON ord.order_id=position.order_id
WHERE ord.status='o1'
AND ( position.status='p15' OR position.status='p16' OR position.status='p17' OR position.status='p18')
AND position.order_id NOT IN (SELECT order_id FROM position
WHERE status IN ('p1', 'p2', 'p3', 'p4', 'p5', 'p6', 'p7', 'p8', 'p9', 'p10', 'p11', 'p12', 'p13', 'p14')
AND position.order_id = ord.order_id) order by order_id

so sollte glaub ich richtig sein. Wenn nicht mach ich mir hier jetzt gleich tabellen dafür

[samic]

yeah, ich hab gleich ein paar Stichproben gemacht und bei allen hat es gestummen!

Herzlichen Dank!!!

Aber noch was: Kann es sein, dass diese Abfrage extrem viel mehr Zeit in anspruch nimmt? Die Abfrage hat bei mir grad 200 Sekunden gebraucht, kann aber auch sein, dass das Netz überlastet ist.. ;-)

[PHP-Boernie]

Diese Abfrage wird schon ein wenig mehr Zeit in anspruch nehmen. Je nachdem wieviele Datensätze da drin sind. Vielleicht ist es dir ja möglich im SubSelect also dem zweiten Select noch ein paar einschränkungen zu machen. z.b. nur alle Status die nicht älter als 30 Tage oder so sind. Dann sollte es wieder schneller werden. Anders gehts aber glaub ich kaum. Sonst muss dir mal jemand anders helfen der sich noch besser damit auskennt.

Wobei mir fällt grad ein das du einen Index in der Position Tabelle auf Status und OrderID setzen kannst. Also beide Felder zu einem Index machen. Vielleicht bringt das nochmal nen Geschwindigkeitsschub.