SQL – select where for specific id, all distinct values for other column exist

I am having a bit of a hard time explaining the logic I want to achieve in the title.

Anyway, I have a database containing two tables (rent and car).

The car table contains data about cars (license number and such) and currently is:

lno,myear,comp,doors,shift,boot,seats
12345678,2020,Ford,4,0,500,5
23456789,2019,renault,5,0,1000,7
98765432,2018,Ford,5,1,500,5
13371337,2000,renault,4,1,500,5

The rent table contains data about rents went in the rental agency:

cid, rno, lno, fromdate, todate, wid, amount
55,1,12345678,2020-04-02,2020-04-11,99,3750
55,1,98765432,2020-04-02,2020-04-11,98,4200
54,1,23456789,2020-10-15,2020-10-17,97,1200
55,1,13371337,2020-04-02,2020-04-11,99,1337

I want to query the cid, rno (client_id, request number) that ordered in this specific request cars from all companies available (ford and renault).

I achieved it by creating an array of companies rented for every cid,rno and compared it to an array of all companies available:

with all_companies as (
    select array_agg(distinct comp) as all_distinct_companies
    from car
),
     rents_and_companies as (
         select cid, rno, array_agg(distinct comp) as rent_all_companies
         from rent,
              car
         where rent.lno = car.lno
         group by cid, rno)
select *
from rents_and_companies
where rent_all_companies = (select all_distinct_companies from all_companies)

returns 55,1,"{Ford,renault}" because client 55 ordered in request 1 a car from ford and renault (12345678, 13371337).

problem is, I am not allowed to use array_agg in class, what is an other way to achieve this functionality?

thanks

Answer

If you want to avoid array aggregation, you could write the query as follows:

select r.cid, r.rno
from rent r
inner join car c on c.lno = r.lno
group by r.cid, r.rno
having count(distinct c.comp) = (select count(distinct comp) from car)

Advertisement

Answer