Problem with IN and WHERE 1

[mastermonster]

I have a Access 2000 database, but I don't know SQL so much. My query is above and sorry for table and column names are not english....

SELECT d.adi as Adi, SUM(c1.tutar) as t1,SUM(c2.tutar) as t2, SUM(c3.tutar) as t3
FROM ( ( ( ( (
dagitimYeri as d
LEFT JOIN cikislar as c1 ON c1.dagitimYeri = d.id)
LEFT JOIN malzemeler as m1 ON m1.id = c1.malzemeRef )
LEFT JOIN cikislar as c2 ON c2.dagitimYeri = d.id )
LEFT JOIN malzemeler as m2 ON m2.id = c2.malzemeRef )
LEFT JOIN cikislar as c3 ON d.id = c3.dagitimYeri )
LEFT JOIN malzemeler as m3 ON m3.id = c3.malzemeRef
WHERE m1.id IN (SELECT id FROM malzemeler WHERE stokBolumu = 1) AND
m2.id IN (SELECT id FROM malzemeler WHERE stokBolumu = 2) AND
m3.id IN(SELECT id FROM malzemeler WHERE stokBolumu = 3)
GROUP BY d.adi


I want to see all value in "dagitimYeri" table , When I drop "WHERE", it return all value, but i should use "IN" for "malzeme" table in WHERE clause. So no value is returned, What can I use for instead of "IN" clause or how can change this sql to returns all value in first table? Any suggestion

 

[PHV]

You may try something like this:
SELECT d.adi as Adi, SUM(Nz(c1.tutar,0)) as t1, SUM(Nz(c2.tutar,0)) as t2, SUM(Nz(c3.tutar,0)) as t3
FROM ( ( ( ( (
dagitimYeri as d
LEFT JOIN cikislar as c1 ON c1.dagitimYeri = d.id)
LEFT JOIN (SELECT id FROM malzemeler WHERE stokBolumu = 1) as m1 ON m1.id = c1.malzemeRef )
LEFT JOIN cikislar as c2 ON c2.dagitimYeri = d.id )
LEFT JOIN (SELECT id FROM malzemeler WHERE stokBolumu = 2) as m2 ON m2.id = c2.malzemeRef )
LEFT JOIN cikislar as c3 ON d.id = c3.dagitimYeri )
LEFT JOIN (SELECT id FROM malzemeler WHERE stokBolumu = 3) as m3 ON m3.id = c3.malzemeRef
GROUP BY d.adi


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mastermonster]

Thanks for message, it returns all value in "dagitimYeri" but now I have another problem, All SUM(....) value in one row is same and their value is so big, how can I solve this?