I think I have it:
SELECT TT.TEILENR, TT.BEZEICHNUNG, LS.MENGE, TT.MINB, LS.TYP, TT.TYP FROM TTEILE TT LEFT JOIN TLAGER_SUM LS ON TT.TEILENR = LS.TEILENR AND LS.TYP = 1 WHERE TT.TYP=1 AND (LS.MENGE IS NOT NULL AND LS.MENGE<tt.minb OR TT.MINB>0 and ls.menge is null) ORDER BY TT.TEILENR; The and ls.typ = 1 should exclude all records from tlager_-sum there not typ 1? It was fine!!!!! Thank you Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Donnerstag, 28. August 2014 14:55 An: firebird-support@yahoogroups.com Betreff: AW: [firebird-support] Difficult condiion question Hello SET, first, thank you! [Minb] should be tteile.minb the minimum inventory amount. Can you tell me what about the statements before the where conditions? Until now I dont know about. Your syntax returns me too many records, some of them has entries in the table tlager_sum, but the amount and the present typ (1) returns me this statement, it is null. I have a table with stock = tlager_sum. Only records with typ 1 I need, many of them has two records for each material, typ 1 and typ 2. In my case I just need the records with typ 1 and now I have the problem, If there is an entry for typ 2, my code does not return this record. For example: Tteile.teilenr = PIECE1, tteile.minb = 1000 In the table tlager_sum is a record, teilenr PIECE1, Menge (amount) = 100, typ = 2 In this case I would return this Teilenr because it is not present and the minb (minimum inventory amount) is set. Thans J >Hello, > >the following sql statement is almost done, just one thing is problematic. > >In the table tlager_sum I have two types of amounts, one of typ 1, one of typ 2. I just need the one of typ1. But if the material not in the table listet >or just für typ 2, and there is a minimum inventory in the tteile set, then should I get this record too. > >Expect for one constellation it works fine: It was set a minimum inventory and in the table tlager_sum It was present a record with an amount of typ 2 - >then I don't get this record. How can I solve this problem in firebird? In Microsoft Access I can create a subquery how gets me only typ 1, whats in >firebird the best way? >SELECT TTEILE.TEILENR, TTEILE.BEZEICHNUNG, TLAGER_SUM.MENGE, TTEILE.MINB, TLAGER_SUM.TYP, TTEILE.TYP >FROM TTEILE LEFT JOIN TLAGER_SUM ON TTEILE.TEILENR = TLAGER_SUM.TEILENR >WHERE (((TLAGER_SUM.MENGE)<[minb]) AND ((TLAGER_SUM.TYP)=1) AND ((TTEILE.TYP)=1)) OR (((TLAGER_SUM.MENGE) Is Null) AND ((TTEILE.MINB)>0) AND ((TTEILE.TYP)=1)) >ORDER BY TTEILE.TEILENR; Hi Olaf, I don't quite understand your question, but it is possible that you just need to add 'AND TLAGER_SUM.TYP = 1' to your left join. Below is the sql after I tried to tidy it a bit and simplify to see if that helped me understand your question (by the way, I don't understand [minb], is that some kind of parameter?). HTH, Set SELECT TT.TEILENR, TT.BEZEICHNUNG, LS.MENGE, TT.MINB, LS.TYP, TT.TYP FROM TTEILE TT LEFT JOIN TLAGER_SUM LS ON TT.TEILENR = LS.TEILENR AND LS.TYP = 1 AND LS.MENGE<[minb] AND LS.TYP=1 WHERE TT.TYP=1 AND (LS.MENGE IS NOT NULL OR TT.MINB>0) ORDER BY TT.TEILENR;
