[SOLVED] MySQL Count

[Ken2k7]

Hey,

Having these 2 database table structures:

 

CREATE TABLE IF NOT EXISTS `user` (
     `id` int( NOT NULL AUTO_INCREMENT
     `name` varchar(20) NOT NULL,
      PRIMARY KEY (`id`)
);

 

CREATE TABLE IF NOT EXISTS `cars` (
     `id` int( NOT NULL AUTO_INCREMENT
     `user_id` int( NOT NULL,
      PRIMARY KEY (`id`)
);

 

What I want to do is select all `user` who has less than 3 `cars` (or in this case, less than 3 columns in the `cars` table).

 

Can someone help me with this query?

 

Thanks!

[fenway]

select user_id, count(*) as cnt from cars group by user_id having cnt < 3

[Ken2k7]

Thanks, is this right:

 

<?php
$sql = "SELECT `cars`.user_id, COUNT(*) as cnt  
  FROM `user`, `cars` 
  GROUP BY `cars`.user_id 
  HAVING `user`.name = 'ken' 
	AND `cnt` < '3' ";
?>

[fenway]

Not exactly... no join condition!

 

Try:

 

select c.user_id, count(*) as cnt from cars as c inner join user as u on ( u.id = c.user_id ) group by c.user_id having cnt < 3

[Ken2k7]

Thanks