Returning results based on related record conditions in a table

Other SQL Server Topics
1

I am a relatively new SQL user, trying to manage an MRP system for a manufacturing company. I have signed up for a couple of Access and SQL classes through Udemy but have not completed them yet. I am hoping to get some help on a specific issue to keep me moving forward in fixing our mismanaged database. I'm not up to speed on SQL terminology so I'm not even sure if my topic makes sense. Anyway, I have a table "OrderDet" that contains various information about customer orders including,

JobNo, PartNo, ProdCode, PartDesc, Status, MasterJobNo, Etc

The product that we sell to our customer are considered ProdCode = 'CUST PART'. Customer parts can be made up of sub-assemblies ("SUB ASSEM") or sub-components ("SUB COMP"). All CUST PART's, SUB ASSEM's and SUB COMP's have their own JobNo in the table. All SUB ASSEM's and SUB COMP's are tied to their CUST PART using the MasterJobNo field. When any JobNo is complete, its status changes from OPEN to CLOSED. For years the system was used improperly and the SUB parts were not getting closed when they were done. As a result, our "Open Order Report" shows several open jobs that are actually done and gone, creating confusion for the manufacturing managers. I am trying to create a Query that finds all SUB COMP's and SUB ASSEM's that are open where the MasterJobNo Job is closed. Conceptually this is an easy thing to accomplish. Its the verification of the MasterJobNo 'Status' that I don't know how to do. Below is the simple Query that gives me the first step, but I don't know how to tie in the MasterJobNo "Status" to eliminate all the jobs where the SUB COMP is Open and the CUST PART (MasterJobNo) is still Open.

SELECT *
FROM OrderDet
WHERE ProdCode = 'SUB COMP'
AND Status = 'OPEN'
ORDER BY JobNo

Do I need to perform a nested SELECT statement and somehow pass the MasterJobNo from the first Query into the second? Any help here would be appreciated, and sorry if this is tough to read. Hopefully the SQL courses will help with the terminology.

2

It's hard without some sample data, but you could try something like this"

SELECT *
FROM OrderDet
WHERE ProdCode in ('SUB COMP','SUB ASSEM')
AND Status = 'OPEN'
and JobNo not in (select JobNo from OrderDet where ProdCode not in ('SUB COMP','SUB ASSEM') AND Status = 'CLOSED')
ORDER BY JobNo

1 Like
3

That seems to be close. First of all, there is one catch that I didn't really outline that will cause this to not work properly. That is the fact that SUB COMP's can often times have MasterJobNo that are SUB ASSEM's, which then in turn have MasterJobNo that are CUST PART's. The same condition applies where I don't want open SUB COMP's that report to closed SUB ASSEM's, but I am simply going to ignore these instances for now since this entire Query will become unnecessary once we have the offending jobs removed from the system. Everything moving forward is being handled the proper way so we are not creating new jobs that have this issue.

I haven't fully wrapped my head around the nested SELECT statements to understand all the criteria that are being asked of the system, but I am getting many returned records that are not quite right. It captures a lot of OPEN subs that report to CLOSED customer parts (good) but it also seems to capture OPEN subs for OPEN customer parts as well (bad). I'm not sure, but I don't see how this query can work without referencing the MasterJobNo anywhere. It almost seems like I need to perform some kind of transform in the nested SELECT statement to look for JobNo in OrderDet where JobNo = MasterJobNo from the outer SELECT. It appears that the statement you provided looks for JobNo's that are CUST PART's and OPEN, then the outer statement looks within those results for JobNo's that are SUB COMP or SUB ASSEM and are CLOSED. I really need the inner SELECT to look for the MasterJobNo from the outer select. Sorry, I'm thinking out loud here. I'll see if I can create a small sample database to provide some sample data. Here is a rough idea of what I'm dealing with.

Capture

In that example, I want to return 18000-05 and 18000-06 because they are SUB's that are OPEN with MasterJobNo that is CLOSED (The customer part has shipped, so the components used to make it must also be complete already). I do not want to return 18000-02 and 18000-03 because their MasterJobNo is still open (the customer part has not shipped, so the components used to make them may still be in-process). Does this clarify things at all?

4

Just need to change the in part for comparing the subselect.

Drop table if exists #t
go
create table #T (OrderNO int, JobNO varchar(20), PartNO varchar(20), ProdCode varchar(20), Status varchar(6), MasterJobNo varchar(20))
go

insert into #t values 
(18000,'18000-01','CUST01','CUST PART','OPEN',null),
(18000,'18000-02','SUB01','SUB COMP','OPEN','18000-01'),
(18000,'18000-03','SUB02','SUB COMP','OPEN','18000-01'),
(18000,'18000-04','CUST02','CUST PART','CLOSED',null),
(18000,'18000-05','SUB03','SUB COMP','OPEN','18000-04'),
(18000,'18000-06','SUB04','SUB COMP','OPEN','18000-04')


SELECT *
FROM #t
WHERE ProdCode in ('SUB COMP','SUB ASSEM')
AND Status = 'OPEN'
and MasterJobNo in (select JobNo from #t where ProdCode not in ('SUB COMP','SUB ASSEM') AND Status = 'CLOSED')
ORDER BY JobNo
5

OK, that makes sense. I wasn't sure if the sub-select returned all of the columns in the recordset to be able to apply criteria to. At least for now, it looks like this will work for what I'm doing and really helps me to understand how to do other similar tasks that need to be done in our system. Thank you so much for the guidance!