CODEWITHSUNDEEP
mysql
Monica Heddneck
Monica Heddneck

Reputation: 3135

Check for the existence of more than one value in a field in MySQL

Say I have a simple table

cust    location
001     USA
001     India
002     Ranchacucamunga
002     Ala-Lemu

I want to find anyone that has been to both USA and India.

I'm thinking something like:

SELECT 
CASE location
WHEN location is both USA and India 
THEN foo
ELSE bar
END

I don't know how to do the WHEN part. And I don't know where to put the GROUP BY!

In my experience, MySQL just wants to return rows. How can I get MySQL to check for the existence of more than one value in a field?

EDIT

both solutions are equally good I just accepted the earlier one.

Upvotes: 0

Views: 35

Answers (2)

W.Prins
W.Prins

Reputation: 1316

Normally you would use INTERSECT for this, but as INTERSECT is not available in MySQL you must use equivalent JOIN's instead, or use counting as has already been suggested:

select a.cust from 
( select cust from simple_table t1 where t1.location = 'USA') a
inner join
( select cust from simple_table t2 where t2.location = 'India' ) b on a.cust = b.cust

You can write that better as follows (which is more readable to my eyes):

select 
  cust 
from 
  simple_table t1 
inner join
  simple_table t2 using (cust)
where 
  t1.location = 'USA' and t2.location = 'India';

Upvotes: 2

John Ruddell
John Ruddell

Reputation: 25862

You can use a where statement to specify your locations

SELECT * 
FROM table
WHERE location IN ('USA', 'India')

If you want customers that have been to both do this

SELECT cust, group_concat(location)
FROM table
WHERE location IN ('USA', 'India')
GROUP BY cust
HAVING count(*) > 1

For customers that have been to usa and india at a minimum of one time you can do this

SELECT cust, GROUP_CONCAT(location) as locations, sum(location = 'USA') as num_loc, sum(location = 'India') as num_loc1
FROM table
WHERE location IN ('USA', 'India')
GROUP BY cust
HAVING count(*) > 1 AND num_loc > 0 AND num_loc1 > 0

note you can change the > 0 to be any number of times. if you want customers that have visited usa twice and india twice just change HAVING count(*) > 1 AND num_loc > 0 AND num_loc1 > 0 to HAVING count(*) > 1 AND num_loc > 1 AND num_loc1 > 1

FIDDLE

Upvotes: 1

Related Questions