MSSQL Query For Single Record In One-To-Many Relationship
Posted on 2014-01-30
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