Home > IT Answers > SQL Server > SQL sub query or where exists
Bartbart
50 pts.
 SQL sub query or where exists
SQL Server 2003, SQL Server Query, Sub Query, Windows Server 2003
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))



Software/Hardware used:
Server 2003 , winSql
ASKED: February 22, 2011  2:38 PM
UPDATED: June 11, 2013  3:23 PM
RELATED QUESTIONS
Answer Wiki:
OK, you need to learn how to join tables together, and how to format code on the web. Without having access to your schema I came up with this. Also your table and column names suck. 
<pre>select DISTINCT PMPRNO,PMMTNO as Material
from MVXJDTA.MPDMAT
JOIN MVXJDTA.MITBAL ON MVXJDTA.MPDMAT.PMMTNO = MVXJDTA.MITBAL.MBITNO
AND MBSUNO = 'AMER NAT'
and MBSTAT ='20'
JOIN MVXJDTA.MITSTA ON MVXJDTA.MITSTA.MHITNO = MVXJDTA.MITBAL.MBITNO
AND MHCYP6 Between 200801 and 201102
AND MHMAQT <> 0
AND MHPUQT <> 0
AND MHSOQT <> 0
AND MHUSQT <> 0
AND MHDEMQ <> 0</pre>
Also, there's no such thing as SQL Server 2003. You either have SQL Server 2000 or SQL Server 2005.
Last Wiki Answer Submitted:  June 11, 2013  3:23 pm  by  Michael Tidmarsh   14,060 pts.
All Answer Wiki Contributors:  Michael Tidmarsh   14,060 pts. , Denny Cherry   64,550 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

MrDenny.,
Thanks for the reply. You are correct in the fact that I have “a LOT” to learn.

As for the server… not sure what I am looking at but the server I am on is a Windows Server 2003 R2.
Wikipedia “SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.”)

Thanks again for the info and I have reviewed the code provided. I did run it just to see but I am coming up with a “0″. Appears the issue is the “AND” statements. I changed the “AND”s to an “OR” and I receive the same results that I had prior to changing the code. The data is correct except for the fact of the MBSTAT “20″. It pulls all items that have the relationship and not just the ones with the status “20”.

Sorry for the table and column names. It is an ERP system. I didn’t create the database that is for sure

Thanks again and let me know if you have any other ideas.

Bartbart
 50 pts.
 

Just to clarify:

“the server I am on is a Windows Server 2003 R2.”

That is the operating system version, it is not the version of the DBMS.

Wikipedia “SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.”)

That is the version/revision of the SQL language. The SQL standard is implemented (with some limitations and some particular additions) by most database vendors (SQL Server, Oracle, DB2, etc).

SQL Server is not a language, it is a database management system by Microsoft, and as Mrdenny stated, there is no Sql Server 2003.

As for your query, the fact that you tried changing ANDs to ORs denotes that you don’t understand what the suggested query does, and don’t understand how those conditions work in a WHERE clause. I would seriously recommend looking for a “SQL basics”-like tutorial to learn the basics of WHERE clauses, and only after having a good understanding on the subject, go back to your query and fix it.

carlosdl
 63,580 pts.
 

Understood.
Sorry for the troubles.
I changed the “and”s to “or” because I thought it would give me the data if any of the fields are not equal to “0″. Bases upon the “and”, it is my understanding that all conditions must be met when I only care if one of the conditions are met.
I have reviewed a few tutorials but it appears that as you suggest I need to do it again.
Thanks again and I apologize for the inconvenience

Bartbart
 50 pts.
 

No apology necessary, we are here to help. That was just a suggestion, that will make your job easier.

You might want to post your original query again using the editor’s “code” tool, and your tables’ structure. Maybe more help can be provided with that information.

carlosdl
 63,580 pts.