SQL query join with conditions-CodePudding

I have the following statement:

Obtain the number and name of the departments that don't have employees living in MADRID. Order the result ascendently by the name of the departments.

And the tables are:

CREATE TABLE DEPARTAMENTS
         (  NUM_DPT INTEGER,
    NOM_DPT CHAR(20),
    PLANTA INTEGER,
    EDIFICI CHAR(30),
    CIUTAT_DPT CHAR(20),
    PRIMARY KEY (NUM_DPT));

CREATE TABLE PROJECTES
         (  NUM_PROJ INTEGER,
    NOM_PROJ CHAR(10),
    PRODUCTE CHAR(20),
    PRESSUPOST INTEGER,
    PRIMARY KEY (NUM_PROJ));

CREATE TABLE EMPLEATS
         (  NUM_EMPL INTEGER,
    NOM_EMPL CHAR(30),
    SOU INTEGER,
    CIUTAT_EMPL CHAR(20),
    NUM_DPT INTEGER,
    NUM_PROJ INTEGER,
    PRIMARY KEY (NUM_EMPL),
    FOREIGN KEY (NUM_DPT) REFERENCES DEPARTAMENTS (NUM_DPT),
    FOREIGN KEY (NUM_PROJ) REFERENCES PROJECTES (NUM_PROJ));

INSERT INTO  DEPARTAMENTS VALUES (3,'MARKETING',3,'RIOS ROSAS','MADRID');

INSERT INTO  PROJECTES VALUES (1,'IBDTEL','TELEVISIO',1000000);

INSERT INTO  EMPLEATS VALUES (3,'ROBERTO',25000,'ZAMORA',3,1);

So the query I performed is:

select departaments.NUM_DPT, NOM_DPT from departaments 
inner join (
    select distinct NUM_DPT
    from empleats 
    where ciutat_empl != 'MADRID') xd
on departaments.num_dpt = xd.num_dpt
order by nom_dpt asc;

However, the departments without employees must appear in the solution, and I don't know how to accomplish that. Any clues?

CodePudding user response：

If you change the formulation of your requirement to SQL-friendly one:

Select departments such that there does not exist any employee in that department who lives in MADRID.

then I guess you get what you want (didn't try it).

select d.NUM_DPT, d.NOM_DPT
from departaments d
where not exists (
  select 1
  from empleats e
  where e.ciutat_empl = 'MADRID'
    and e.num_dpt = d.num_dpt
)

CodePudding user response：

One option for you is to try with UNION,use another query to find departments without employees

select departaments.NUM_DPT, NOM_DPT from departaments 
inner join (
    select distinct NUM_DPT
    from empleats 
    where ciutat_empl != 'MADRID') xd
on departaments.num_dpt = xd.num_dpt
UNION
select departaments.NUM_DPT, NOM_DPT from departaments WHERE num_dpt
NOT IN(select distinct NUM_DPT
    from empleats )
order by nom_dpt asc;