CODEWITHSUNDEEP
sqldatabasepostgresql
Yusuf Ibrahim
Yusuf Ibrahim

Reputation: 1619

Only show users that having cars more than one

Dear all I have users table and cars table.

and I have following join query:

select 
 users.id as user_id,
 users.username,
 users.job,
 cars.id,
 cars.brand as car_brand

FROM users 
 LEFT JOIN cars on users.id = cars.user_id

GROUP BY users.username, users.id, cars.id;

Here is the snapshot:

enter image description here

How to query for users that having cars more than one?

I tried code below but it return empty data:

enter image description here

How to get users that having more than one cars? (username: Ismed)

Upvotes: 0

Views: 964

Answers (4)

Arjun Viradia
Arjun Viradia

Reputation: 1

Suppose you have two table Car and Owner. Car table fields: carID,Brand,Model,Year,Type. Owner table fields: ownerID,firstName,lastName,DOB,address,email,phone.

Car table has primary key carID and a foreign key ownerID from Owner table.

Here we are using Common Table Expression (CTE) to accomplish this task. A CTE act as a temporary table to hold partial results.

  • Firstly this part will give all the records intersecting car and owner
  • Secondly we are just filtering records having more than one car.

With OwnerWithMultipleCarCTE as ( Select firstName,lastName, Count(*) as TotalCars From Owner o, Car c Where o.ownerID = c.ownerID Group By o.firstName,o.lastName)

Select * From OwnerWithMultipleCarCTE Where TotalCars > 1;

Upvotes: 0

Jagan Sivakumar
Jagan Sivakumar

Reputation: 11

SELECT users.username
FROM users
WHERE users.id IN(
    select 
    users.id
    FROM users 
    JOIN cars on users.id = cars.user_id
    GROUP BY users.id
    HAVING COUNT(*) > 1
);

Filter users first who has more then one car then get corresponding details

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The simplest and probably most performance method is to use window functions:

select user_id, username, job, id, brand
from (select u.id as user_id, u.username, u.job,
             c.id, c.brand as car_brand,
             count(*) over (partition by u.id) as num_cars
      from users u join
           cars c
           on u.id = c.user_id
     ) uc
where num_cars > 1;

Note that I changed the left join to a join. If you have two matches, you are requiring a match. I also introduced table aliases so the query is easier to write and to read.

Upvotes: 1

Avi
Avi

Reputation: 1845

You can do in this was as well. 

    select 
     users.id as user_id,
     users.username,
     users.job,
     cars.id,
     cars.brand as car_brand

    FROM users 
     LEFT JOIN cars on users.id = cars.user_id
    where exists (select username, count(*) multiplecars        
    FROM users u
      JOIN cars c on u.id = c.user_id
where users.username = u.username 
     group by 
      u.username 
having count(*) > 1 )

If the users have more than one car (even if same brand then this will bring those records) if you only want users with more than one branded care you can do count(distinct)

Upvotes: 2

Related Questions