Using Sub-query result in where condition

Question

I'm having an issue with a potentially simple query I can't get working. I'm also not entirely sure on how to Google it. So here it goes.

I have 2 tables:

TableA - Holds the main records I need to return
TableB - Holds many records per record in TableA

All I need to do is return records from TableA that match a query such as:

SELECT Description, Type, Status
FROM TableA
WHERE Status = 2;

but with this extra bit on the end (I know the query is wrong, but hopefully it gives you a clure as to what I'm after (PSEUDO CODE):

SELECT ID, Description, Type, Status
FROM TableA
JOIN TableB ON TableB.ID = TableA.ID
WHERE Status = 2
AND (MIN(TableB.StatusChanged) > DATEADD(minute, -15, GETDATE()))
AND TableB.Status < 5;

So essentially I want to return all records from TableA as long as they have a status of 2 in TableA, and the earliest record in TableB is at least 15 minutes old, and has a status value less than 5.

Hopefully I have explained it in a way you can understand what I'm trying to accomplish.

can you show some sample data and the expected output?

Vamsi Prabhala
– Vamsi Prabhala

2018-10-14 21:59:30 +00:00
Commented Oct 14, 2018 at 21:59 — Vamsi Prabhala
– Vamsi Prabhala, Commented Oct 14, 2018 at 21:59
Please read this for some tips on improving your question.

HABO
– HABO

2018-10-15 00:55:13 +00:00
Commented Oct 15, 2018 at 0:55 — HABO
– HABO, Commented Oct 15, 2018 at 0:55

jtate · Accepted Answer · 2018-10-14 23:27:50Z

1

Use CROSS APPLY to simulate a join and get only the first match:

SELECT a.Description, a.Type, a.Status, b.*
FROM TableA a
    CROSS APPLY (
        SELECT TOP 1 *
        FROM TableB b
        WHERE a.ID = b.ID -- the join condition
            AND b.Status < 5
            AND b.StatusChanged < DATEADD(MINUTE, -15, GETDATE()
        ORDER BY b.StatusChanged -- the earliest one that matches
    ) b
WHERE a.Status = 2;

edited Oct 14, 2018 at 23:27

answered Oct 14, 2018 at 22:07

jtate

2,7067 gold badges28 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-10-14 22:17:29Z

0

I think apply is a good approach, but the logic would seem to be:

SELECT a.Description, a.Type, a.Status, b.*
FROM TableA a CROSS APPLY
     (SELECT TOP 1 *
      FROM TableB b
      WHERE a.ID = b.ID AND -- the join condition            
            b.StatusChanged < DATEADD(MINUTE, -15, GETDATE())
      ORDER BY b.StatusChanged -- the earliest one that matches
     ) b
WHERE a.Status = 2 AND b.Status < 5;

answered Oct 14, 2018 at 22:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

jtate Over a year ago

This wouldn't return a row if there are two B records that fit in the 15 minute window and the earlier one has a status >= 5 and the other is < 5. I guess it depends on his needs exactly, but the way I read the question is how I wrote my answer.

jtate Over a year ago

Oof, I just noticed your date filter is correct and mine was wrong :)

TomC · Accepted Answer · 2018-10-14 23:11:46Z

0

I would just turn your check around and use a not exists

SELECT ID, Description, Type, Status
FROM TableA
JOIN TableB ON TableB.ID = TableA.ID
    AND TableB.Status < 5
    AND not exists (
        select * from TableB B2 where B2.ID=TableB.ID and B2.StatusChanged < DATEADD(minute, -15, GETDATE())
        )
WHERE TableA.Status = 2

answered Oct 14, 2018 at 23:11

TomC

2,8241 gold badge10 silver badges17 bronze badges

Collectives™ on Stack Overflow

Using Sub-query result in where condition

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related