CODEWITHSUNDEEP
sqldatabasepostgresqljoinsubquery
Pradeep
Pradeep

Reputation: 1223

select owner who has all types of bikes

Below is picture of two tables, owners and vehicles. I need to find out the owner who has all types of bikes.
enter image description here Example: O_id 100,O_id 101,O_id 102 have bikes with V_id=1, but
O_id 103 has all types of bikes(V_id = 1 and V_id = 5)
How to write a query to get these details?
My Query:

select o.o_id from owner o,vehicles v where
o.v_id = v.v_id where v_type = 'bike'

this is showing all owners who have a bike,but not owners who have all bikes

Upvotes: 1

Views: 480

Answers (3)

abhish_gl
abhish_gl

Reputation: 401

Here are two queries using subquery and join query:

schema:

drop table vehicle;
drop table owner;
create table vehicle(V_id int, V_type varchar(20));
create table owner(O_id int , V_id int);
insert into vehicle values(1,'Bike');
insert into vehicle values(2,'Car');
insert into vehicle values(3,'Car');
insert into vehicle values(4,'Car');
insert into vehicle values(5,'bike');
insert into owner values(100, 1);
insert into owner values(101, 1);
insert into owner values(102, 1);
insert into owner values(103, 1);
insert into owner values(100, 2);
insert into owner values(101, 3);
insert into owner values(103, 5);

subquery:

postgres=# select count(*) as bike_count, O_id  from owner where V_id in (
postgres(#    select V_id from vehicle where upper(V_type) = 'BIKE'
postgres(# )
postgres-# group by 2
postgres-# order by 1 desc
postgres-# ;
 bike_count | o_id
------------+------
      2 |  103
      1 |  101
      1 |  100
      1 |  102
(4 rows)

Join query:

postgres=# select count(*) as bike_count, O_id  from owner o join vehicle v using(v_id)
postgres-# where  upper(v.V_type) = 'BIKE'
postgres-# group by 2
postgres-# order by 1 desc
postgres-# ;
 bike_count | o_id
------------+------
      2 |  103
      1 |  100
      1 |  102
      1 |  101
(4 rows)

If you want just the owner, you can limit the queries to 1

Upvotes: -1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

with bykes as (
    select array_agg(v_id) as byke_ids
    from vehicle
    where lower(v_type) = 'byke'
), owner as (
    select o_id, array_agg(v_id) as v_ids
    from owner
    group by o_id
)
select o_id
from owner cross join bykes
where v_ids @> byke_ids
;
o_id 
------
103

The schema:

create table owner (
    o_id int,
    v_id int
);
create table vehicle (
    v_id int,
    v_type text
);
insert into owner (o_id, v_id) values
(100, 1),
(101, 1),
(102, 1),
(103, 1),
(100, 2),
(101, 3),
(103, 5);

insert into vehicle (v_id, v_type) values
(1, 'Byke'),
(2, 'Car'),
(3, 'Car'),
(4, 'Car'),
(5, 'byke');

Upvotes: 0

juergen d
juergen d

Reputation: 204894

Group by the o_idthat you want to get.
Take only those groups having the same number (count(v_id)) of bikes that exist in total (select count(*) from vehicles where v_type = 'bike')

select o.o_id 
from owner o
join vehicles v on o.v_id = v.v_id
where v.v_type = 'bike'
group by o.o_id 
having count(distinct v.v_id) = (select count(*) from vehicles where v_type = 'bike')

Upvotes: 2

Related Questions