want to find out the book_id which is written by a single author only and p_id=1

Question

I have 3 tables.

This is the Person table:

This is the Car table:

This is Person_car table hloding join column for many to many:

Now i want to find out the c_id (car id) that is owned by a single person and p_id=1.

That means in this scenario i should get c_id as 100 bcs c_id 200 is owned by p_id 1 and 2

Please help me out to write the sql query.

Answer 1

looking at your question I understand that you want to find Cars owned by ONLY one person / and p_id = 1 is just an example/. If this is the case, please try:

select
   c.id as car_id,
   c.color as car_color,
   c.model as car_model,
   p.id as person_id,
   p.first_name || ' ' || p.last_naem as person_name,
from
   car c
   join person_car pc on (pc.c_id = c.id)
   join pesron p on (p.id = pc.p_id)
where
   c.id in (select c_id from peson_car group by c_id having count(1) = 1)

Thanks.