Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-30
4
Medium Priority
?
760 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

916 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