Issue select statement on multiple rows

Question

I use DB2.

Situation: I want do do a query on my table RELATIONS to list ALL the companies that have a RELATION 1 AND a RELATION 2 OR 3 assigned. In my DB design, 1 or more companies could have multiple relations.

I want to do a select statement with multiple AND operators on the same column (RELATION) with SQL but if i execute the code i do not get any hits.

SELECT R_ID, COMPANY_NAME from RELATION
WHERE COMPANY_GROUP = 2245
AND RELATION = 1
AND RELATION in (2,3)

When i execute this i don't get any hits.

This is my DB design.

***This is the 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 is the table COMPANY

COMPANY_ID, COMPANY_NAME, COMPANY_ADDRESS, COMPANY_GROUP
31          Jupiler       Some address     2245
32          Unox          Some address     2245
33          Lotus         Some address     2245
34          Lu            Some address     2245
35          Felix         Some address     2245
36          Unicoresels   Some address     2245
37          Sporkampt     Some address     2245

This is the result i want to achieve with a query.

R_ID, COMPANY_NAME
123   Unox
124   Unox
127   Felix
128   Felix
129   Unicoresels
130   Unicoresels

How can i do this?

A row which has RELATION = 1 can't have 2 or 3 at the same time. — jarlh
– jarlh, Commented Sep 10, 2017 at 15:09

Gordon Linoff · Accepted Answer · 2017-09-10 15:11:40Z

One approach is to use group by and having:

SELECT COMPANY_NAME 
FROM RELATION
WHERE RELATION IN (1, 2, 3)
GROUP BY COMPANY_NAME
HAVING SUM(CASE WHEN RELATION = 1 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN RELATION IN (2, 3) THEN 1 ELSE 0 END) > 0 ;

Notes:

If you want to filter by company group, then you need to join in the companies table.
The relation table should be using COMPANY_ID, not COMPANY_NAME.

EDIT:

If you want the rows from the RELATION table that match, then a simple method is to use the above as a subquery:

SELECT r.*
FROM RELATION r
WHERE r.COMPANY_NAME IN (SELECT r2.COMPANY_NAME 
                         FROM RELATION r2
                         WHERE r2.RELATION IN (1, 2, 3)
                         GROUP BY r2.COMPANY_NAME
                         HAVING SUM(CASE WHEN r2.RELATION = 1 THEN 1 ELSE 0 END) > 0 AND
                                SUM(CASE WHEN r2.RELATION IN (2, 3) THEN 1 ELSE 0 END) > 0
                        );

Collectives™ on Stack Overflow

Issue select statement on multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related