CODEWITHSUNDEEP
mysqlsql
maine
maine

Reputation: 3

SQL How to select columnA and columnB where columnB has a distinct value

I have a table called emp

id| name  
1 | jon  
1 | trav  
2 | jon  
2 | jon    
3 | jon
3 | jon

I would like to select only the distinct ids that have the value "jon" in the name column.
I do not want the ids where there is one value "jon" and another value as something else within the name column.

The output should look like this:

id|name  
2 |jon  
3 |jon

Upvotes: 0

Views: 92

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I would use:

select id, min(name) as name
from emp
group by id
having min(name) = max(name) and min(name) = 'Jon';

Upvotes: 0

yg-dba
yg-dba

Reputation: 333

A NOT EXISTS clause will work well here, and perform well too.

SELECT id, name
FROM tbl t
WHERE name = 'jon'
AND NOT EXISTS (SELECT 1 FROM tbl a WHERE a.id = t.id AND a.name != t.name)

Upvotes: 0

Arneev
Arneev

Reputation: 49

SELECT id, name
FROM emp
WHERE name = 'jon'
GROUP BY id, name
HAVING COUNT(*) > 1

Upvotes: 0

George Joseph
George Joseph

Reputation: 5922

You may use the following

The logic is to find out the total count of names and the count of names by id where name='jon' and if it matches return the output.

Here is one way to do this.

--Using max since we need an aggregate function when grouping by id, to bring any other columns.

create table t(id int, name varchar(50))

insert into t 
select 1,'jon'  union all
select 1,'trav' union all 
select 2,'jon'  union all
select 2,'jon'  union all  
select 3,'jon'  union all
select 3,'jon'

select id,max(name)
  from t 
 group by id
having count(distinct name)=count(distinct case when name='jon' then 1 end)

+----+-----------+
| id | max(name) |
+----+-----------+
|  2 | jon       |
|  3 | jon       |
+----+-----------+

Db Fiddle link https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=637432cca3238285cb888889e1fa6ec7

Upvotes: 1

Related Questions