CODEWITHSUNDEEP
sql
Adam Hartnett
Adam Hartnett

Reputation: 27

SQL Multiple records with foreign key. Need to verify that all are present with conditions to return answer

I have a table t1 that I'm trying to return all foreign keys that have a record with status a-e where a-d have dates and e doesn't. Each foreign key will relate to roughly 10 records(shortened for ease). The status' are not unique so each FK can have multiple repeats of the status.

FK Status Date
123 a date1
123 b date2
123 c date3
123 d date4
123 e null
456 a null
456 b date6
456 c date7

Attempt 1:

Select FK, sum(case when status = 'a' and date is not null then 1 else 0 end +
   case when status = 'b' and date is not null then 1 else 0 end +
case when status = 'c' and date is not null then 1 else 0 end +
case when status = 'd' and date is not null then 1 else 0 end + 
case when status = 'e' and date is null then 100 else 0 end) as sum_status
from t1
group by FK 
order by sum_status desc;

In this example I get FK = 123, 456 sum_status = 104, 2

This is a less than ideal solution as its showing every record for the FK whereas I only want the ones that match the criteria so I still need to use trial and error to get the right one. I'm trying at the moment to write a cursor to loop through and check that there's one of each required status. I'm a relative beginner with SQL so I'm sure there's an easy way to do this so if I could get a tip or a point in the right direction please.

Upvotes: 1

Views: 34

Answers (1)

Craig
Craig

Reputation: 1226

A simple enough solution is to just use some EXISTS sub-queries to confirm your various conditions - the only question might be how much data you actually have, and how the indexing is set, as to whether you'll have a poor-performing query or not. (You could also achieve pretty much the same results by JOIN-ing a set of the tables together, with the "FK" column as the JOIN predicate)

Example:

SELECT 
    DISTINCT 
        FK 
FROM t1 t1a
WHERE [Status] = 'a' AND [Date] Is Not Null
AND EXISTS
(
    SELECT 1 FROM t1 t1b WHERE t1b.FK = t1a.FK
    AND t1b.[Status] = 'b'
    AND t1b.[Date] Is Not Null
)
AND EXISTS
(
    SELECT 1 FROM t1 t1c WHERE t1c.FK = t1a.FK
    AND t1c.[Status] = 'c'
    AND t1c.[Date] Is Not Null
)
AND EXISTS
(
    SELECT 1 FROM t1 t1d WHERE t1d.FK = t1a.FK
    AND t1d.[Status] = 'd'
    AND t1d.[Date] Is Not Null
)
AND EXISTS
(
    SELECT 1 FROM t1 t1e WHERE t1e.FK = t1a.FK
    AND t1e.[Status] = 'e'
    AND t1e.[Date] Is Null
)

Upvotes: 1

Related Questions