Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

Need SQL Query Assistance

Need help with an SQL Query today.

Have HeaderTable and ItemTable linked by Header ID - H_ID (See illustration below)

Need SQL Query to return HeaderTable.PO_NUm and the first ItemTable.Descr for each Headertable item where BPO=True and Status='A' and Ven_ID=201

 SampleQuery tables
Sql would be something like:

select PO_NUM, Descr FROM HeaderTable H JOIN ItemTable T on H.H_ID=T.h_ID
WHERE BPO=True and Status='A' and Ven_ID=201 ORDER BY PO_NUM

Open in new window


How do I get it to return only 1st ItemTable Descr for each qualified HeaderTable row?
Also, need Blank Descr returned if no Itemtable row for qualified HeaderTable row.

Help is appreciated.
0
trims30
Asked:
trims30
  • 4
  • 3
1 Solution
 
Bill BachPresidentCommented:
This sounds like a homework problem, which is kind of against the EE guidelines, so instead of giving an answer, we'll just point you in the right direction.  What you need is a correlated subquery, using a TOP 1 statement.

Re-think this as a subquery instead of a JOIN and I think you'll see the answer.  If you still get stuck, take your best shot at it, and we'll see what you need tweaking after that.
0
 
trims30Author Commented:
Bill:
Am new to SQL - just trying to rewrite an old VB6/Btrieve6.15 program to vb.net/SqlServer.

Don't know enough about subqueries to make this work otherwise I wouldn't be asking for help.

Lee
0
 
Bill BachPresidentCommented:
Why would you possibly want to re-write a classic Btrieve application?  I bet it will still run today, as-is, and be faster than anything else out there!  

Try something like this:

SELECT PO_NUM, (SELECT TOP 1 Descr FROM ItemTable I WHERE H.H_ID = I.H_ID) AS Descr
FROM HeaderTable H
WHERE BPO=True and Status='A' and Ven_ID=201 ORDER BY PO_NUM

Note that the "first" record you get may vary.  If you want to specify what you mean by "first", add an ORDER BY to the subquery.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
trims30Author Commented:
Bill:
I'll try your suggestion later tonite and let you know how it goes..

The old Btrieve stuff we use (vb6 w/btrieve primatives) runs much faster than SQL and that too is a concern.  

My end users don't care what's under the hood as long as it delivers.  Their IT guys however, don't like it when they see old dll's being used in Win7 environment.  They just don't understand it.  

Lee
0
 
trims30Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for trims30's comment #a38423807

for the following reason:

Bill:<br />Added the WHERE Clause to subquery and it works great.<br />Thanks for teaching me something.<br /><br />Lee<br />aka TRIMS30 from Btrieve Forums
0
 
Bill BachPresidentCommented:
If we were able to answer the quyestion, is there any reason that you are closing it without assigning any points?
0
 
trims30Author Commented:
Bill:
I did accept solution and it should have awarded you the 100 points.
Don't know why you didn't get points on my acceptance.

Something got screwed up somewhere.

Lee
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now