CODEWITHSUNDEEP
sql-server-express
KastelA Kastel
KastelA Kastel

Reputation: 83

SQL how to show list of people with no orders

I'm trying to solve this SQL query (it's in French)

I've got 4 different tables : CLIENT , COMMANDE (order), DETAIL (of the orders) and PRODUIT (product)

I have to give the number(NCLI) and the name(NOM) of the clients of 'Namur'(that is in the LOCALITE) that haven't made any orders (COMMANDE) in a query

I've tried different things, last code i had in my SQL express was this one (wich of course doesn't work.

 select NCLI, NOM
 from   CLIENT
 where  LOCALITE = 'Namur' and (NCLI in (select NCLI             
                                   from COMMANDE
                                   where NCOM in (select NCOM
                                                  from DETAIL
                                                  where QCOM not in DETAIL)))

Could anyone help me out with this?

in case it is needed this is the code of my existing tables and of the inserts:

create table CLIENT
(NCLI char(4) not null,
 NOM  varchar(12) not null,
 ADRESSE varchar(20) not null,
 LOCALITE varchar(12) not null,
 CAT char(2),
 COMPTE decimal(9,2) not null,
 primary key (NCLI));

create table PRODUIT
(NPRO char(5) not null,
LIBELLE varchar(20) not null,
PRIX decimal(5,0) not null,
QSTOCK decimal(6,0) not null,
primary key (NPRO));

  create table COMMANDE
 (NCOM char(5) not null,
 NCLI char(4) not null,
 DATECOM datetime not null,
 primary key (NCOM),
 foreign key (NCLI) references CLIENT);

 create table DETAIL
 (NCOM char(5) not null,
 NPRO char(5) not null,
 QCOM decimal(4,0) not null,
 primary key (NCOM,NPRO),
 foreign key (NCOM) references COMMANDE,
 foreign key (NPRO) references PRODUIT);




 insert into CLIENT values ('B112','HANSENNE'    ,'23, a. Dumont'       ,'Poitiers' ,'C1',1250.00);
 insert into CLIENT values ('C123','MERCIER'     ,'25, r. Lemaitre'     ,'Namur'    ,'C1',-2300);
 insert into CLIENT values ('B332','MONTI'       ,'112, r. Neuve'       ,'Geneve'   ,'B2',0);
 insert into CLIENT values ('F010','TOUSSAINT'   ,'5, r. Godefroid'     ,'Poitiers' ,'C1',0);
 insert into CLIENT values ('K111','VANBIST'     ,'180, r. Florimont'   ,'Lille'    ,'B1',720);
 insert into CLIENT values ('S127','VANDERKA'    ,'3, av. des Roses'    ,'Namur'    ,'C1',-4580);
 insert into CLIENT values ('B512','GILLET'      ,'14, r. de l''Ete'    ,'Toulouse' ,'B1',-8700);
 insert into CLIENT values ('B062','GOFFIN'      ,'72, r. de la Gare'   ,'Namur'    ,'B2',-3200);
 insert into CLIENT values ('C400','FERARD'      ,'65, r. du Tertre'    ,'Poitiers' ,'B2',350);
 insert into CLIENT values ('C003','AVRON'       ,'8, ch. de la Cure'   ,'Toulouse' ,'B1',-1700);
 insert into CLIENT values ('K729','NEUMAN'      ,'40, r. Bransart'     ,'Toulouse' ,NULL,0);
 insert into CLIENT values ('F011','PONCELET'    ,'17, Clos des Erables','Toulouse' ,'B2',0);
 insert into CLIENT values ('L422','FRANCK'      ,'60, r. de Wepion'    ,'Namur'    ,'C1',0);
 insert into CLIENT values ('S712','GUILLAUME'   ,'14a, ch. des Roses'  ,'Paris'    ,'B1',0);
 insert into CLIENT values ('D063','MERCIER'       ,'201, bvd du Nord'  ,'Toulouse' ,NULL,-2250);
 insert into CLIENT values ('F400','JACOB'       ,'78, ch. du Moulin'   ,'Bruxelles','C2',0);

 insert into PRODUIT values ('CS262','CHEV. SAPIN 200x6x2',  75,  45);
 insert into PRODUIT values ('CS264','CHEV. SAPIN 200x6x4', 120,2690);
 insert into PRODUIT values ('CS464','CHEV. SAPIN 400x6x4', 220, 450);
 insert into PRODUIT values ('PA45' ,'POINTE ACIER 45 (1K)',105, 580);
 insert into PRODUIT values ('PA60' ,'POINTE ACIER 60 (1K)', 95, 134);
 insert into PRODUIT values ('PH222','PL. HETRE 200x20x2',  230, 782);
 insert into PRODUIT values ('PS222','PL. SAPIN 200x20x2',  185,1220);

 insert into COMMANDE values ('30178','K111','20081221');
 insert into COMMANDE values ('30179','C400','20081222');
 insert into COMMANDE values ('30182','S127','20081223');
 insert into COMMANDE values ('30184','C400','20081223');
 insert into COMMANDE values ('30185','F011','20090102');
 insert into COMMANDE values ('30186','C400','20090102');
 insert into COMMANDE values ('30188','B512','20090103');

 insert into DETAIL values ('30178','CS464',25);
 insert into DETAIL values ('30179','PA60',20);
 insert into DETAIL values ('30179','CS262',60);
 insert into DETAIL values ('30182','PA60',30);
 insert into DETAIL values ('30184','CS464',120);
 insert into DETAIL values ('30184','PA45',20);
 insert into DETAIL values ('30185','PA60',15);
 insert into DETAIL values ('30185','PS222',600);
 insert into DETAIL values ('30185','CS464',260);
 insert into DETAIL values ('30186','PA45',3);
 insert into DETAIL values ('30188','PA60',70);
 insert into DETAIL values ('30188','PH222',92);
 insert into DETAIL values ('30188','CS464',180);
 insert into DETAIL values ('30188','PA45',22);

Upvotes: 0

Views: 126

Answers (2)

user3125485
user3125485

Reputation: 11

why not try this:

select NCLI,NOM
      from CLIENT
      WHERE LOCALITE = 'NAMUR' AND NOT EXISTS(
                                            SELECT *
                                            FROM COMMANDE 
                                            WHERE CLIENT.NCLI = COMMANDE .NCLI)

Upvotes: 1

hashbrown
hashbrown

Reputation: 3516

Does this work?

select c.NCLI, c.NOM
 from   CLIENT c
 where  c.LOCALITE = 'Namur' and c.NCLI NOT IN  (select NCLI             
                                   from COMMANDE)

Upvotes: 0

Related Questions