How to change query to nested select?

Question

I have a project where I have to use nested select and operators like EXISTS, IN, ALL, ANY, to find the name of the owners who own more apartments.

I have the 2 tables owners and apartments

Owner
"ID" NUMBER(5,0), 
"NAME" VARCHAR2(20), 
"PHONE" NUMBER(10,0),
CONSTRAINT "PROPR" PRIMARY KEY ("ID")
USING INDEX  ENABLE; 

Apartment
"ID_AP" NUMBER(5,0), 
"ADDRESS" VARCHAR2(35), 
"SURFACE" NUMBER(10,0),
"ID" NUMBER(5,0),
CONSTRAINT "APART" PRIMARY KEY ("ID_AP")
USING INDEX  ENABLE;

In order to find the owners who have more than one apartment, I wrote this

SELECT name, id, count(id)
from apartment join owner
using (id)
group by id, name
having count(id)>1

But how do I use nested Selects and one of the operators EXISTS, IN, ALL, ANY?

first of all why you need to use nested select instead of join? — noodlesegg, Nov 30 '16 at 12:41

score 0 · Answer 1 · answered Nov 30 '16 at 12:44

0

For example like this:

select * from owner where id IN (
    SELECT id
    from apartment 
    group by id
    having count(id)>1)

answered Nov 30 '16 at 12:44

arturro

1,598
1
10
13

score 0 · Answer 2 · answered Nov 30 '16 at 12:46

I am not quite sure that the query you wrote is correct .

The correct query that you provided would count(id_ap) instead of count(id)

SELECT o.id, o.name, count(a.id_ap)
from apartment a
join owner o on o.id = a.id
group by o.id, o.name
having count(a.id_ap)>1

The same query using in clause, but without the number of apartments, you only know that it's 2 or more

select o.id, o.name
from owner o
where o.id in (select distinct(a.id) from apartment a
group by a.id having count(a.id_ap) > 1);

You are missing a foreign key constraint on apartment table referencing owner table as well.

score 0 · Answer 3 · answered Nov 30 '16 at 13:00

WITH myView(name, id,appcount)
as
SELECT name, id, (select count(id) FROM apartment where id = ow.id) 
FROM owner ow

SELECT * from myView
WHERE appcount > 2

Create a inline View using WITH and use to write a query. (select count(id) FROM apartment where id = ow.id) in select of outer query will calculate apartment count.

How to change query to nested select?

3 Answers3