CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Salman
Salman

Reputation: 57

SQL Join Query Selection

Please look at the below tables and result. Check my query and help me to get the result as 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 query:

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

Upvotes: 4

Views: 60

Answers (3)

balint.steinbach
balint.steinbach

Reputation: 426

Dont know what Attachment means but if you want another column for your join result you can use this. The result will contain only the rows that are required

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

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460118

If i understand your requirement you want only rows from table incident and an additonal column if there are attchments, which is the case if there is at least one record in table act_reg with the same incident_id + usr_id and 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

Demo

Upvotes: 4

Steven Powell
Steven Powell

Reputation: 36

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'
               )

Upvotes: 0

Related Questions