Query with subquery

Question

Query with subquery

Navigation

#1 by (1 votes)

0

I need to answer this question: 4) Search for the name and address of the customers who have all the films in the store.

I can not test the query, but would it be something like this?

SELECT C.nome, C.endereço from CLIENTES C
WHERE EXISTS (
  SELECT * FROM filmes F
  WHERE EXISTS (
    SELECT * from locaçoes L
    where F.id = L.filme and C.id = L.cliente
  )
)

sql

asked by anonymous 15.10.2016 / 18:13

1 answer

Print the absolute url in a view with AspNetCore Error: Undefined index: Password in C and error Strict standards: Only variables should be passed by reference

score 1 · Accepted Answer

In order to list the clients who have already rented absolutely all the films from the catalog of the store, one can use (among others) the following query:

SELECT C.nome, C.endereço FROM CLIENTES C
WHERE (SELECT COUNT(DISTINCT Filme) FROM Locações WHERE cliente = C.id) 
      = (SELECT COUNT(1) FROM Filmes)

What this query does is count the number of rentals, without considering rented movies more than once ( COUNT(DISTINCT Filme) ), and list only those that count equal to the total number of movies in the store.