CODEWITHSUNDEEP
postgresql
user721588
user721588

Reputation:

postgres query (more than function)

Let's assume that I have 2 tables: Human, Car. Table Car has some columns and one of them is Human_id. For example, like this:

Human_id Type
1        BMW
5        Mercedes
1        Audi
2        Peugeot
3        BMW
2        JEEP

How can I select all humans who have more than one car? I mean I need to control if Human_id is used more than once in the Human_id column. But how I can do that?

SELECT human.name
FROM Human, Car
WHERE .... <- what goes here??

Upvotes: 1

Views: 157

Answers (4)

user731136
user731136

Reputation:

If you have a FK between Human and Car tables, it's best to use a Join instead of a Cartesian product:

create table Human (
  idHuman integer primary key,
  name varchar(15),
   ...
);
insert into Human (idHuman, name) values
  (1,'A'), (2,'B'), (3,'C'), (4,'D'), (5,'E');

create table Car (
  Human_id integer REFERENCES Human ON DELETE CASCADE,
  Type varchar(15),
  ...
);
insert into Car (Human_id, Type) values 
  (1,'BMW'), (5,'Mercedes'), (1,'Audi')
 ,(2,'Peugeot'), (3,'BMW'), (2,'JEEP');

And the query:

select hu.name
from Human hu
join Car ca on (ca.Human_id = hu.idHuman)
group by hu.name
having count(*) > 1

I hope that I help you

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

SELECT human.name, car.human_id, count(*) as total
FROM Human, Car
group by human.name, car.human_id
having count(*) > 1

Upvotes: 1

Florin Stingaciu
Florin Stingaciu

Reputation: 8295

 select human.name
 from human
 where human.id in
      select human.id
      from car
      where count(car) > 1
      group by human.id

Upvotes: -1

lanzz
lanzz

Reputation: 43198

Try grouping your Car records by Human_id, and take a look at the HAVING clause.

Upvotes: 1

Related Questions