Try this
SELECT * FROM tzinterst
WHERE tzinterst.ZINTERNR IN
(
SELECT tzinterl.ZINTERNR FROM tzinterl
WHERE tzinterl.YHNUMMER IN
(
SELECT tzstoffl.YHNUMMER FROM tzstoffl
WHERE tzstoffl.ZNUMM IN
(
SELECT tzspez.ZNUMM FROM tzspez WHERE ZLNUMM = '008096'
)
)
) order by tzinterst.ZART;
[EDIT in response to OP comments]
All I did above was correct your syntax error. To make it more performant and read better try the following (caveat - I haven't been able to check this)
SELECT INTERST.*
FROM tzspez SPEZ
LEFT OUTER JOIN tzstoffl STOFF1 ON SPEZ.ZNUMM = STOFF1.ZNUMM
LEFT OUTER JOIN tzinterl INTER1 ON STOFF.YHNUMMER = INTER1.YHNUMMER
LEFT OUTER JOIN tzinterst INTERST ON INTER1.ZINTERNR = INTERST.ZINTERNR
WHERE SPEZ.ZLNUMM = '008096' ORDER BY INTERST.ZART
Couple of other things to note ... you really should not do things like
= '" + txt_ZLNUMM.Text + "'"
as this leaves you vulnerable to SQL Injection attacks (and also makes it harder to debug complex sql statements).
Use Parameters instead - here's an article to get you started from
dotnetperls[
^]
You may need to tune up your database by using Indexes on ZNUMM, YHNUMMER etc - useful
free book - Use the index Luke[
^]