Solved

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

Posted on 2014-01-30
4
733 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 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