CODEWITHSUNDEEP
sqloracle-database
user3384231
user3384231

Reputation: 4053

SQL query extraction: same row with same id

I am bit stuck on the following. The data of the table as follows:

  sid   eid  description   returncode    responsemessage   State
    1   T-1                200            OK               Sent 
    1   T-1  Helloworld                                    Processed

I cannot use stored procedure, the application only supports a SQL query.

select * 
from table 
where eid='T-1' 
  and returncode='200' 
  and returnmessage='OK' 
  and state='Sent'

May be something needs to be added here??

Any tips or ideas on how I can achieve this with SQL query?

Update: Oracle Database, I want to retrieve "HelloWorld" from the description column but it should be only retrieved when State=Sent has returncode=200 and responsemessage=ok

Upvotes: 0

Views: 58

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15140

I'm not entirely clear on what you are trying to accomplish, but maybe you meant something like this:

select T1.* 
from table  T1
INNER JOIN table T2
        ON  T2.SID = T1.sid
where T1.eid='T-1' 
  and T1.returncode='200' 
  and T1.returnmessage='OK' 
  and T1.state='SENT'
  AND T2.description IS NOT NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I think you can do this using exists:

select t.*
from t
where t.description is not null and t.eid = 'T-1' and
      exists (select 1
              from t t2
              where t2.eid = t.eid and t2.returncode = '200' and
                    t2.returnmessage = 'OK' and t2.state = 'SENT' 
             );

You might want to include equality on sid as well, but that is not clear from the question.

Upvotes: 0

Related Questions