CODEWITHSUNDEEP
mysqlsql
Vish
Vish

Reputation: 4492

Return a list of people that have only car, motorbike or car and motorbike

Table 1

Name, Vehicle
P1, Car
P1, Motorcylce
P1, Truck
P1, Helicopter
P2, Car
P3, Motorcycle
P4, Motorcycle
P4, Car
P5, Car
P5, Truck
P6, Motorcycle
P6, Truck
P7 Truck

How do I query the table above so that only people with with either have a car, or a motorbike or must have both the car and the motorbike are returned.

So only,

P2 -> only has a car (valid)
P3 -> only has a motorcyle (valid)
P4 -> has both car and motorcycle (valid)

are returned

Upvotes: 0

Views: 478

Answers (5)

jarlh
jarlh

Reputation: 44796

Return people EXCEPT those having a type of vehicle that is not a car or a motorcycle.

select name from tablename
except
select name from tablename where vehicle not in ('Car', 'Motorcycle')

An extra WHERE clause may speed things up:

select name from tablename where vehicle in ('Car', 'Motorcycle')
except
select name from tablename where vehicle not in ('Car', 'Motorcycle')

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would use NOT EXISTS :

select t.*
from table t
where not exists (select 1 
                  from table t1 
                  where t1.name = t.name and 
                        t1.Vehicle in ('Helicopter', 'Truck')
                 );

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I would write this as:

select name
from yourtable
where vehicle in ('car', 'motorbike')
group by name
having count(*) = 2;

This assumes that there is only one row per type. If not, use count(distinct):

select name
from yourtable
where vehicle in ('car', 'motorbike')
group by name
having count(distinct vehicle) = 2;

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

Here's one approach with conditional aggregation:

select name
from yourtable
group by name
having count(case when vehicle = 'Car' then 1
                  when vehicle = 'Motorcycle' then 1
             end) = count(*)

If you can have people with more than one car or motorcycle that you don't want to be returned (i.e. P8 has 2 cars), use count(distinct vehicle) instead to exclude those records. Not clear with your posting if that matters or not.

Upvotes: 2

HamzaNig
HamzaNig

Reputation: 1029

You can try this query :

select Name from table 
where Name  not in (select distinct Name from table where Vehicle in 
('Helicopter','Truck'))

Upvotes: 0

Related Questions