SQL Query Selection

Question

Please review the tables below and the result. Check my query and help me get the result as shown below.

Table : incident
----------------
incident_id   usr_id    item_id
10059926       191       61006

Table: act_reg
--------------
act_reg_id  act_type_id  incident_id    usr_id  act_type_sc
454244         1        10059926         191    ASSIGN
471938        115       10059926         191    TRAVEL TIME
473379        40        10059926         191    FOLLOW UP
477652        115       10059926         191    TRAVEL TIME
477653        107       10059926         191    SITE ARRIVAL
489091      5000054     10059926         191    ADD_ATTCHMNTS

Result(Need to get)
-------------------
incident_id   usr_id    item_id  Attachment
10059926       191      61006     Yes

My request:

SELECT incident.incident_id,incident.usr_id,incident.item_id,Attachemt
FROM incident RIGHT JOIN act_reg ON incident.incident_id=act_reg.incident_id

Answer 1

If I understand your requirement, you only need rows from the table incidentand an extra column if there are bindings, which is the case if the table act_reghas at least one record with the same incident_id+ usr_idand act_type_sc=ADD_ATTCHMNTS.

I would use CASE WHEN EXISTS:

SELECT incident_id, usr_id, item_id,
       Attachment = CASE WHEN EXISTS
                    (
                       SELECT 1 FROM act_reg a
                       WHERE i.incident_id  =   a.incident_id
                        AND  i.usr_id       =   a.usr_id
                        AND  a.act_type_sc  =  'ADD_ATTCHMNTS'
                    ) THEN 'Yes' ELSE 'No' END
FROM incident i

Answer 2

Something like this should help

SELECT  incident.incident_id,incident.usr_id,incident.item_id,
        'Yes' Attachemt
  FROM  incident 
  where exists (select * from act_reg 
                  where incident.incident_id = act_reg.incident_id
                    and act_reg.act_type_sc  = 'ADD_ATTCHMNTS'
               )

Answer 3

, , , .

SELECT incident.incident_id,incident.usr_id,incident.item_id, 'yes'
FROM incident, act_reg WHERE incident.incident_id = act_reg.incident_id;