CODEWITHSUNDEEP
sql-server
user2749152
user2749152

Reputation: 1

SQL query to join 2 tables and get specific results

I have 2 tables that I need to do a join, Batch and Sample. I have to select all of the Batches that don't have data in the FinishedTS field with the condition that all of the samples of that batch(from Sample table) are in Status field with value 'Acknowledeged'. Here are the tables that I have. Using the example tables I would like a query that will return kpID 2004, it is the only Batch kpID that fits the conditions. I would appreciate any help with coming up with a query that would accomplish this.

table - Batch

kpID  BatchNumFull     ACC_Number FinishedTS  
1186  15-322.2001-242  322.2001   2015-10-07 17:18:00
2012  15-322.2001-246  322.2001   2015-10-13 15:58:00
2000  15-999-001       999 
2004  15-999-002       999
2038  15-999-004       999

table - Sample

kfBatchID   Status        SampleID
2038        Acknowledged  0001
2038                      0002
2038        Delivered     0003
2004        Acknowledged  0077
2004        Acknowledged  0078
2004        Acknowledged  0034

Upvotes: 0

Views: 42

Answers (2)

Noor
Noor

Reputation: 165

The following sql will give you the required output but will not guarantee a record in Sample with Acknowledged status.

SELECT * FROM Batch 
WHERE FinishedTS IS NULL
AND NOT EXISTS (SELECT 1 FROM Sample 
                WHERE Batch.kpId = Sample.kfBatchId
                AND [status] <> 'Acknowledged')

However, the following will gaurantee that there is atleast one record in Sample with Acknowledged status

SELECT * FROM Batch 
WHERE FinishedTS IS NULL
AND EXISTS (SELECT 1 FROM Sample 
            WHERE Batch.kpId = Sample.kfBatchId
            AND [status] = 'Acknowledged')
AND NOT EXISTS (SELECT 1 FROM Sample 
                WHERE Batch.kpId = Sample.kfBatchId
                AND [status] <> 'Acknowledged')

Upvotes: 1

Brian Stork
Brian Stork

Reputation: 980

This approach creates a list of BAD kfBatchID values and selects only the Batch records that are NOT on the bad list, but are also on the good list. A Common-table-expression will find the bad values (anything with a status not "Acknowledged"). The left join excludes these records. Joining back to Sample makes sure the id actually has a sample (if you don't do this, record 2000 is included):

So it looks a little like this:

;with ExcludeBatchId
as (
    select distinct
           kfBatchID
      from [Sample]
     where [status] <> 'Acknowledged'
   )
   select distinct
          [Batch].*
     from [Batch]
     join [Sample]
       on [Sample].[kfBatchID] = [Batch].[kpID]
left join ExcludeBatchId
       on ExcludeBatchId.[kfBatchID] = [Batch].[kpID]
    where [Batch].FinishedTS is null
      and ExcludeBatchId.[kfBatchID] is null

Now the performance my not be that great because it has two distinct in there and the where in the CTE is an inequality operator. But it will give you the records according to your criteria.

Upvotes: 0

Related Questions