I am working on with SQL server 2003 and using winsql to access and run the query. In the following query I am trying to get the data for a Part (PMPRNO) that the Material (PMMTNO) is from the supplier "AMER NAT" an has a status (MBSTAT) of "20". One other parameter is I only want to see the data if the Part (PMPRNO) has a value >0 in the MITSAT table. the following query give me what I want with the exception of it shows me all data if the MITSTA table has useage (>0)for the PMPRNO or the PMMTNO. How do I get it to select just the items that have a usage for the PMPRNO.
select DISTINCT PMPRNO,PMMTNO as Material from MVXJDTA.MPDMAT where MVXJDTA.MPDMAT.PMMTNO in (select MBITNO from MVXJDTA.MITBAL where MBSUNO = 'AMER NAT' and MBSTAT ='20'AND MVXJDTA.MITBAL.MBITNO in (Select MHITNO from MVXJDTA.MITSTA where MHCYP6 Between 200801 and 201102 Group by MHITNO Having SUM(MHMAQT+MHPUQT + MHSOQT + MHUSQT + MHDEMQ) >0))
Server 2003 , winSql
February 22, 2011 2:38 PM
June 11, 2013 3:23 PM