SQL sub query or where exists

50 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bartbart
    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.
    50 pointsBadges:
    report
  • carlosdl
    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.
    65,110 pointsBadges:
    report
  • Bartbart
    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
    50 pointsBadges:
    report
  • carlosdl
    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.
    65,110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following