>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;
