StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL search for only one LEFT item
text
Body
copied!<p>My SQL query really takes a lot of time, so I thought there might be a better way of writing it.</p> <p>The table "Vorgangdetails" has an item, which has a number in "RecId". This number might be in a different row in "WeiterfuehrungVonPos". If so this new row has a number in "RecId", which might be again in a different row in "WeiterfuehrungVonPos" ... (overall up to 3 times) My query simply takes way too long. Do know a faster way?</p> <pre><code>SELECT SUM(VD_AB.Menge) AS Amount, Artikel.Artikelnummer AS Artikelnummer, FROM Artikel INNER JOIN Vorgangdetails AS VD_AB ON LEFT(VD_AB.Auftrag, 3) = 'AF-' AND Artikel.Artikelnummer = VD_AB.Artikel </code></pre> <p>--> adding maximum 1 result from the same table as VD_AB</p> <pre><code> LEFT JOIN Vorgangdetails AS VD_L ON LEFT(VD_L.Auftrag, 3) = 'LF-' AND Artikel.Artikelnummer = VD_L.Artikel AND VD_L.WeiterfuehrungVonPos = VD_AB.RecId </code></pre> <p>--> again ... adding maximum 1 result from the same table as VD_AB</p> <pre><code> LEFT JOIN Vorgangdetails AS VD_RvL ON VD_L.RecID = VD_RvL.WeiterfuehrungVonPos AND LEFT(VD_RvL.Auftrag, 3) = 'RE-' </code></pre> <p>--> again ... adding maximum 1 result from the same table as VD_AB</p> <pre><code> LEFT JOIN Vorgangdetails AS VD_RvAB ON VD_AB.RecID = VD_RvAB.WeiterfuehrungVonPos AND LEFT(VD_RvAB.Auftrag, 3) = 'RE-' WHERE (VD_RvAB.Auftrag = '' AND VD_RvL.Auftrag = '') GROUP BY(Artikelnummer) </code></pre> <p>. .</p> <p>Final RESULT) -------------- edited to reflect all answers from Roman Pekar</p> <pre><code>SELECT SUM(VD_AB.Menge) AS Auftragsmenge, Artikel.Artikelnummer AS Artikelnummer, FROM Artikel INNER JOIN Vorgangdetails AS VD_AB ON VD_AB.Auftrag LIKE 'AB-%' AND Artikel.Artikelnummer = VD_AB.Artikel LEFT JOIN Vorgangdetails AS VD_L ON Artikel.Artikelnummer = VD_L.Artikel AND VD_L.WeiterfuehrungVonPos = VD_AB.RecId AND (VD_L.Auftrag LIKE 'LF-%' OR VD_L.Auftrag LIKE 'RE-%') LEFT JOIN Vorgangdetails AS VD_RvL ON VD_L.Auftrag is not null AND VD_L.Auftrag LIKE 'LF-%' AND VD_L.RecID = VD_RvL.WeiterfuehrungVonPos AND VD_RvL.Auftrag LIKE 'RE-%' WHERE (Artikel.Kategorie = 'KARSTADT->Aktive') AND (VD_L.Auftrag LIKE 'LF-%' AND VD_RvL.Auftrag = '') AND NOT (VD_L.Auftrag LIKE 'RE-%') GROUP BY(Artikelnummer) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload