CODEWITHSUNDEEP
sqlpostgresql
polmonroig
polmonroig

Reputation: 1017

SQL statement select columns with specific value

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!

Upvotes: 1

Views: 124

Answers (6)

Baskaran
Baskaran

Reputation: 64

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

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272006

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 2

S-Man
S-Man

Reputation: 23666

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.

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

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

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

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(*);

Upvotes: 1

Related Questions