sql - Issue select statement on multiple rows -

i use db2.

situation: want do query on table relations list companies have relation 1 , relation 2 or 3 assigned. in db design, 1 or more companies have multiple relations.

i want select statement multiple , operators on same column (relation) sql if execute code not hits.

select r_id, company_name relation company_group = 2245 , relation = 1 , relation in (2,3) 

when execute don't hits.

this db design.

***this the table relation

r_id, relation, company_name 121   1            inbev 122   6            jupiler 123   1            unox 124   2`           unox 125   4            lotus 126   1            lu 127   1            felix 128   2            felix 129   1            unicoresels 130   3            unicoresels 131   4            sporkamt 

***this table company

company_id, company_name, company_address, company_group 31          jupiler       address     2245 32          unox          address     2245 33          lotus         address     2245 34          lu            address     2245 35          felix         address     2245 36          unicoresels   address     2245 37          sporkampt     address     2245 

this result want achieve query.

r_id, company_name 123   unox 124   unox 127   felix 128   felix 129   unicoresels 130   unicoresels 

how can this?

one approach use group by , having:

select company_name  relation relation in (1, 2, 3) group company_name having sum(case when relation = 1 1 else 0 end) > 0 ,        sum(case when relation in (2, 3) 1 else 0 end) > 0 ; 

notes:

• if want filter company group, need join in companies table.
• the relation table should using company_id, not company_name.

edit:

if want rows relation table match, simple method use above subquery:

select r.* relation r r.company_name in (select r2.company_name                           relation r2                          r2.relation in (1, 2, 3)                          group r2.company_name                          having sum(case when r2.relation = 1 1 else 0 end) > 0 ,                                 sum(case when r2.relation in (2, 3) 1 else 0 end) > 0                         );