How to find a field with some specific value in another field in sql

Question

I have 2 columns MessageID and FlowStatusID.

I want to find MessageID's which have a FlowStatusID is one specific value with a special sequence.

For example I want to find MessageID's where the FlowStatusID contains the sequence of these numbers: 105,81,21

MessageID   FlowStatusID
-------------------------
    1            11
    1           105
    2           105
    2            81
    2            21
    3            81
    4           105
    4            81
    4            21
    5            21
    5           105

The result must be 2, 4

Because they contain the sequence 105,81,21 in FlowStatusID. @user3237339 Do they have to be in that order also? What defines the order of the records? — NickyvV, Aug 04 '14 at 07:27

Sathish · Answer 1 · 2014-08-04T09:41:19.790

0

Try like this

select MessageIDfrom t group by MessageID having count(*) =3 ;

Or

select MessageID from (
select t.MessageID from t where FlowStatusID=21 
union all 
select t.MessageID from t where FlowStatusID=81 
union  all
select t.MessageID from t where FlowStatusID=105 )
as tt group by MessageID having count(*) =3

edited Aug 04 '14 at 09:41

answered Aug 04 '14 at 07:48

Sathish

4,419
4
30
59

this query returns messagesid that have 21, 81,105 and more in flowstatusid but i want messageid that have just only these 3 numbers: 21, 81,105 not more – user3237339 Aug 04 '14 at 09:37
@user3237339 did you try second query. Check the answer – Sathish Aug 04 '14 at 09:40

score 0 · Answer 2 · edited May 23 '17 at 12:29

You don't mention the database type but I've found some other tickets which explain how to concatenate values from multiple records.

Postgress: Concatenate multiple result rows of one column into one, group by another column

Oracle : SQL Query to concatenate column values from multiple rows in Oracle

You can use this concatenated field in a condition with an equality match:

where myconcatresult = '21,81,105'

I don't know how this will perform :)

How to find a field with some specific value in another field in sql

2 Answers2