Oracle query using 3 tables, sql

Question

I have a problem with a query for Oracle with this scenario:

Table People 
ID | Name 
1 | juan 
2 | pedro 
3 | luis 

Table Properties 
ID | nombre_inmueble | FK to Table People 
1 | house | 1 
2 | garden | 1 
3 | terrace | 1 
4 | moto | 2
5 | jet  | 2 

Table Accessories 
ID | accessories | FK Table Properties
1 | windows | 1 
2 | doors | 1 
3 | scale | 2 
4 | plants | 3 
5 | motor | 4

What I want is only the people who have Properties and that have ALL Accessories, in this case the output would be

1 | juan

What would be the query?

Ftaveras · Accepted Answer · 2014-10-24 02:26:00Z

1

Your query will look like this:

SELECT *
FROM People P
WHERE EXISTS(
        SELECT 1
        FROM Properties T
        WHERE T.PEOPLE= P.ID
)
AND NOT EXISTS(
        SELECT 1
        FROM Properties T
        WHERE T.PEOPLE= P.ID
        AND NOT EXISTS(
                SELECT 1
                FROM Accessories A
                WHERE A.Properties = T.ID
        )
);

edited Oct 24, 2014 at 2:26

answered Oct 24, 2014 at 1:59

Ftaveras

71913 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ftaveras Over a year ago

It show you some error or it produce you an wrong result?

Hernaldo Gonzalez Over a year ago

Wrong result. Its show 2 rows: 1 | juan and 2 | pedro. And I look the query that return only 1 | juan (the only row that have Properties and every Properties has Accesories)

Collectives™ on Stack Overflow

Oracle query using 3 tables, sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related