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??
SELECT human.name, car.human_id, count(*) as total
FROM Human, Car
group by human.name, car.human_id
having count(*) > 1
Try grouping your Car records by Human_id, and take a look at the HAVING clause.
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
select human.name
from human
where human.id in
select human.id
from car
where count(car) > 1
group by human.id
(SELECT id from car GROUP BY id having COUNT(*) > 1)
