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

x
?
Solved

MSSQL Query For Single Record In One-To-Many Relationship

Posted on 2014-01-30
4
Medium Priority
?
763 Views
Last Modified: 2014-01-30
HELLO...

I have (2) tables in MSSQL:

Order_Header, Primary Key = Ord_No

Order_Lines, Primary Key = Ord_No by Seq_No

These tables are obviously related, and Order_Lines is dependent on Order_Header

What we need to extract, is to have a query, where only the *SINGLE* Order_Header.Ord_No record is returned...when ALL the lines in the Order_Lines table, are of a "STATUS" type = "F" (meaning fully shipped).  Note, certainly, there may be many instances where there is a MIXED STATUS OF LINES...i.e., some may be "O" (for Ordered), "B" (for Backordered), etc.  So, in the final result, again, ONLY return the Order_Header.Ord_No, when ALL records Order_Lines.Line_Status = "F".

I'm having trouble writing the proper join for this query...I'm pretty certain this requires some type of aggregate, somewhere, but I just can't get my head around the syntax.

Please help!  I'm in the middle of a system audit, and this will be a tremendous help.  THANK YOU!...Mark
0
Comment
Question by:datatechcorp
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39822625
check this out

;WITH C AS
(
select distinct Line_Status as LS  FROM order_lines WHERE line_status <> 'F'
)
select * from order_header OH
WHERE NOT EXISTS ( SELECT 1 FROM Order_Lines OL where OH.ord_no = OL.ord_no and OL.line_status in (select  LS FROM C) )

Open in new window

0
 

Author Comment

by:datatechcorp
ID: 39822707
Hi Surendra...

Will this produce single records...where ALL those Order Lines are of an Status = "F"?  I'm confused, because the first line in your query, select distinct records...where the status is NOT "F".  Perhaps it's my backwards logic?  Please explain...and THANK YOU!...Mark
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39822731
ok the logic is simple

it will first get all the different statuses from the order_lines which are not F

suppose you have O,B,S,F as your line_statuses then C will hold a table with O,B,S (as I have eliminated F from the list)

Now the Second query will actually check
if for a given order -- the line_status is not in O,B,S then it will be consider that it will be F.. and display...
0
 

Author Comment

by:datatechcorp
ID: 39822739
Ahhh...hahhh!  OK, gotcha...and THANK YOU so much for your expeditious response *and* explanation.  My brain was hurting on this one :-)...

Thanks!...Mark
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question