Question 21110 in Mysql Question posted in
The official MySQL documentation can be found here.

Select distinct columns where another column does not contain a particular value – Mysql

I have a table that looks like this

id name col1
1 foo A
2 foo C
3 bar A
4 bar D

I want to select all names where col1 has no entry D

The result should be "foo"

I tried

SELECT DISTINCT name FROM table WHERE col1 != D

But it returns "foo" and "bar" because of id 3

Tags:

2

Answers


  1. 0

    Avoid the over use of DISTINCT.

    SELECT name
FROM table
GROUP BY name
HAVING SUM(col1='D') = 0

    As an aggregate over names is needed GROUP BY name. HAVING applies after the aggregation. col1='D' is a 1/0 expression so summing them up and you want 0 to be the total.

    Login or Signup to reply.
  2. 0

    Data

    CREATE TABLE mytable(
   id   INTEGER  NOT NULL 
  ,name VARCHAR(40) NOT NULL
  ,col1 VARCHAR(40) NOT NULL
);
INSERT INTO mytable
(id,name,col1) VALUES 
(1,'foo','A'),
(2,'foo','C'),
(3,'bar','A'),
(4,'bar','D');

    use Group_Concat() as follows

    select
name
/*,Group_Concat(col1)  col1 */
from mytable 
group by name
HAVING Group_Concat(col1) not like '%D%'

    dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search