-4

How can i write a sql statement to get customers who have rented at least one film from each category, to Return customer_id, first_name and last_name. from dvdrental

3
  • 6
    1. Please, pick single database engine. 2. Please, provide the definitions for the tables in question. Jan 30 at 18:32
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Jan 30 at 18:37
  • i mean from postgreSQL, I want to query an SQL statement to get customers who have rented at least one film from each category on DVDrental
    – DonnyE
    Feb 6 at 19:22

1 Answer 1

-2
DECLARE @DVDCategories TABLE (CategoryID INT IDENTITY(1,1) Primary Key, CategoryName VARCHAR(50))
INSERT INTO @DVDCategories (CategoryName)
VALUES ('Action'),('Drama'),('Documentories'),('Suspense')

DECLARE @Customer TABLE (CustomerID INT IDENTITY(1,1) Primary Key, CustomerName VARCHAR(20))
INSERT INTO @Customer (CustomerName)
VALUES ('Able'),('Bob'),('Charlie')

DECLARE @DVD TABLE (DVDID INT IDENTITY(1,1) Primary Key, DVDName VARCHAR(50), DVDCategory INT)
INSERT INTO @DVD (DVDName, DVDCategory) 
VALUES ('Cain''s Altar',1),('That''s what she said!',2), ('But how!?',3),('Suddenly',4)

DECLARE @DVDRentals TABLE (DVD INT, Customer INT, RentalDate DATETIME DEFAULT GETDATE())
INSERT INTO @DVDRentals (DVD, Customer)
SELECT DVDID, CustomerId
FROM @Customer C
CROSS JOIN @DVD D
WHERE C.CustomerID = 1
UNION
SELECT DVDID, CustomerId
FROM @Customer C
CROSS JOIN @DVD D
WHERE C.CustomerID = 2
    AND DVDID <> 2
UNION
SELECT DVDID, CustomerID
FROM @Customer C
CROSS JOIN @DVD D
WHERE D.DVDCategory <> 1

SELECT *
FROM @Customer C
WHERE C.CustomerID IN (
    SELECT Customer
    FROM (
    SELECT R.Customer, COUNT(DISTINCT DVDCategory) RentedCategories
    FROM @DVDRentals R
    JOIN @DVD D 
    ON D.DVDID = R.DVD
    GROUP BY R.Customer
    ) CustomerRentalCategoryCount
    WHERE RentedCategories = (
    SELECT COUNT(CategoryID)
    FROM @DVDCategories
    ) --TotalCategories
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.