not equal to - MySQL explicit not having and group

Question

I am looking for SQL syntax that can list "not having / or not equal to values", however my requirement should group results by Name and list only the names that doesn't have "not having / or not equal to values"

When i run

SELECT * FROM promotions WHERE promo_name != 'barbie';

I get following result:

d   Name            promo_name      Remarks
2   Male Adult      Anniversary     NULL
3   Female Adult    Valentines Day  NULL
4   Boy             Play Station    NULL
5   Old Guy         Retirement      NULL
7   Girl            Skirts          NULL
8   Girl            Chocolates      NULL
9   Girl            Christmas       NULL
10  Male Adule      Valentines Day  NULL
11  Male Adule      Christmas       NULL
12  Female Adult    Christmas       NULL
13  Boy             Christmas       NULL

I am looking for a query that generates following result: with condition = WHERE promo_name != 'barbie'

d   Name            promo_name      Remarks
2   Male Adult                      NULL
3   Female Adult                    NULL
4   Boy                             NULL
5   Old Guy                         NULL
6   Old Lady                        NULL

Another example

WHERE promo_name != 'Christmas';

looking for result like below:

2   Male Adult                      NULL
5   Old Guy                         NULL
6   Old Lady                        NULL

I am attaching sql dump below.

CREATE TABLE `test`.`promotions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NULL,
  `promo_name` VARCHAR(45) NULL,
  `Remarks` VARCHAR(100) NULL,
  PRIMARY KEY (`id`));

USE test; 

insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (1,'Girl','Barbie',' ');
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (2,'Male Adult','Anniversary',' ');
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (3,'Female Adult','Valentines Day',' ');
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (4,'Boy','Play Station',' ');
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (5,'Old Guy','Retirement',' ');
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (6,'Old Lady',null,'');
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (7,'Girl','Skirts',null);
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (8,'Girl','Chocolates',null);
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (9,'Girl ','Christmas',null);
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (10,'Male Adule','Valentines Day',null);
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (11,'Male Adule','Christmas',null);
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (12,'Female Adult','Christmas',null);
insert into `promotions`(`id`,`Name`,`promo_name`,`Remarks`) values (13,'Boy','Christmas',null);

You really need to describe what you're looking for a bit more clearly. — cHao, Jan 02 '15 at 05:51
you can use `WHERE promo_name NOT IN('barbie', 'Christmas',....)` if you want use multiple not equal condition — Girish, Jan 02 '15 at 05:54
Your main concern with the group by name right!.can you please describe bit more clear — Vamshi .goli, Jan 02 '15 at 05:57
SELECT * FROM promotions WHERE promo_name NOT IN ('barbie','Christmas'); — fortune, Jan 02 '15 at 05:58
Thanks Grish and Fortune, i understand NOT IN ('{}') works, but there are some situations there may be several hundreds of "not in", is there another option. Hello Vamshi.goli can you Please let me know where and what can i describe more? — P Cing, Jan 02 '15 at 06:02
in this query Another example > " WHERE promo_name != 'Christmas'; " looking for result like below: 2 Male Adult NULL 5 Old Guy NULL 6 Old Lady NULL Here can you explain on what basis you have given result — Vamshi .goli, Jan 02 '15 at 06:18
in the above results of SELECT * FROM promotions WHERE promo_name != 'barbie'..................in the result why you didn't took 'girl' according to your query it has to be there right!!!!!!....the results what you need are no stability — Vamshi .goli, Jan 02 '15 at 06:23
I found right answer already answered earlier (Feb 7 '14 at 16:43). Thank you Barmar [it is available here](http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist) This answer is in fact what i exactly i wanted. Well done Barmar — P Cing, Jan 02 '15 at 06:38

score 1 · Answer 1 · answered Jan 02 '15 at 05:52

1

from what I understand (didn't get your problem entirely, but it seems though) you don't want promo_name column, so don't use *, if you want just two columnns

use their names as

select Name,     Remarks from promotions where promo_name!='barbie'

answered Jan 02 '15 at 05:52

Codeek

1,624
1
11
20

Hi Codeek, result should have 1 unique name that doesn't have specific promo_name. i.e. if i am looking for != 'Christmas' result should have unique users who don't have promo_name 'Christmas' and user should be excluded if he/she is in other promo but not in 'Christmas'. i want result specific to only 1 promo_name only. I wish i can explain better.
this looks simple but please look it up 1 more time.. this is tricky and complex... :) – P Cing Jan 02 '15 at 05:59
I found right answer already answered earlier (Feb 7 '14 at 16:43). Thank you Barmar [it is available here](http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist) This answer is in fact what i exactly i wanted. Well done Barmar – P Cing Jan 02 '15 at 06:36
glad you found it :), happy coding and happy new year – Codeek Jan 02 '15 at 06:50

not equal to - MySQL explicit not having and group

1 Answers1