Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

661 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