Solved

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

Posted on 2014-01-30
4
716 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now