SQL statement select columns with specific value

Question

I need some help making an sql statement; I don't really know how to aproach the situation. I have two tables, Departments and Employees from which I want to select the Dpt_num and the Dpt_name of the departments that have at least one employee and that all their employees are from Barcelona

Case 1

== Departments =======
| Dpt_num | Dpt_name |
|    1    |    A     |
|    2    |    B     |

== Employees ===================
| E_num | Dpt_num |  City      |
|  1    |   1     | Barcelona  |
|  2    |   1     | Barcelona  |

The result in this case should be

 Dpt_num Dpt_name
 ------------------
    1       A

Case 2

== Departments =======
| Dpt_num | Dpt_name |
|    1    |    A     |
|    2    |    B     |

== Employees ==================
| E_num | Dpt_num | City      |
|   1   |    1    | Barcelona |
|   2   |    1    | Madrid    |

The result in this case should be empty.

I tried this for example but it seems very inefficient and it does not work in all the cases

select
    num_dpt, nom_dpt
from
    departements
where
    1 = (select count(distinct e.ciutat_empl)
         from empleats e
         where e.num_dpt = num_dpt)
    and not exists (select * from empleats e
                    where e.ciutat_empl != 'BARCELONA' and e.num_dpt = num_dpt);

I really appreciate any help. Thanks!

. . Have you tried anything? I can think of several different approaches to answering this question. — Gordon Linoff
– Gordon Linoff, Commented Sep 29, 2018 at 14:50

Gordon Linoff · Accepted Answer · 2018-09-29 15:19:31Z

2

You want to go down the path of doing the filtering in the where clause. Then, use exists and not exists:

select d.num_dpt, d.nom_dpt 
from departaments d
where exists (select 1
              from empleats e
              where e.num_dpt = d.num_dpt and e.ciutat_empl = 'BARCELONA' 
             ) and
      not exists (select 1
                  from empleats e
                  where e.num_dpt = d.num_dpt and e.ciutat_empl <> 'BARCELONA' 
             );

The first condition checks that at least one employee is from Barcelona. The second checks that no employees are from any other city.

One major problem in your version is your correlation clause:

e.num_dpt = num_dpt

You think this is doing:

e.num_dpt = departaments.num_dpt

But it is really doing:

e.num_dpt = e.num_dpt

Always qualify your column names. This is especially important when you have more than one table reference in the query.

edited Sep 29, 2018 at 15:19

answered Sep 29, 2018 at 14:58

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kamil Gosciminski Over a year ago

How is it doing e.num_dpt = d.num_dpt if there's no specified d? I think you meant e.num_dpt = e.num_dpt.

Gordon Linoff Over a year ago

@KamilG. . . . Good catch. Thank you.

sticky bit · Accepted Answer · 2018-09-29 14:54:36Z

1

Join the tables, group by the department and check if the count of employees in Barcelona is equal to the count of all employess of the department.

SELECT d.dpt_num,
       d.dpt_name
       FROM departments d
            INNER JOIN employees e
                       ON e.dpt_num = d.dpt_num
       GROUP BY d.dpt_num,
                d.dpt_name
       HAVING count(CASE
                      WHEN e.city = 'Barcelona' THEN
                        1
                    END) = count(*);

answered Sep 29, 2018 at 14:54

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Comments

Kamil Gosciminski · Accepted Answer · 2018-09-29 14:54:47Z

1

I believe this should work:

select d.dpt_num, d.dpt_name
from departments d
inner join employees e on
  d.dpt_num = e.dpt_num
group by d.dpt_num, d.dpt_name
having count(*) = sum(case when e.city = 'Barcelona' then 1 else 0 end)

INNER JOIN makes sure there's at least 1 employee
HAVING count(*) = sum(case when e.city = 'Barcelona' then 1 else 0 end) makes sure that all employees are from Barcelona

answered Sep 29, 2018 at 14:54

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Comments

S-Man · Accepted Answer · 2018-09-29 15:19:30Z

1

demo: db<>fiddle

SELECT dpt_num, dpt_name
FROM (
    SELECT d.dpt_num, d.dpt_name, array_agg(city) as cities
    FROM dept d
    JOIN empl e
    ON d.dpt_num = e.dpt_num
    GROUP BY d.dpt_num, d.dpt_name
) s
WHERE 'Barcelona' = ALL(cities)

Aggregate the cities and then you can filter with the ALL operator which checks if all array elements fit the condition.

answered Sep 29, 2018 at 15:19

S-Man

24k9 gold badges51 silver badges78 bronze badges

Comments

Salman Arshad · Accepted Answer · 2018-09-29 17:47:23Z

1

Generally speaking, you compare COUNT(*) with COUNT(some condition) for such problems:

SELECT *
FROM Departments
WHERE EXISTS (
    SELECT 1
    FROM   Employees
    WHERE  Employees.Dpt_num = Departments.Dpt_num
    HAVING COUNT(*) > 0 -- it is possible to get a 0 if where did not match
    AND    COUNT(*) = COUNT(CASE WHEN Employees.City = 'Barcelona' THEN 1 END)
)

DB Fiddle

edited Sep 29, 2018 at 17:47

answered Sep 29, 2018 at 15:01

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Comments

Kamil Gosciminski · Accepted Answer · 2018-09-29 22:11:43Z

0

Pl try query below

select a.dpt_number,a.dpt_name from yy_department a 
where exists (select 'x' from yy_employees y where y.dpt_number = a.dpt_number and y.city = 'Barcelona') 
and not exists (select 'x' from yy_employees y where y.dpt_number = a.dpt_number and nvl(y.city,'x') <> nvl('Barcelona','y'))

edited Sep 29, 2018 at 22:11

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

answered Sep 29, 2018 at 15:06

Baskaran

643 bronze badges

Collectives™ on Stack Overflow

SQL statement select columns with specific value

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related