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)

2 Answers

0
Avi On
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)

0
Jagan Sivakumar On

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