CODEWITHSUNDEEP
sqlpostgresqlexistssql-view
Brittany
Brittany

Reputation: 119

SQL - Nested Sub-Queries

Using postgresql, I have to create a view called 'no_cat_customers' that returns the first name, last name of any customer who has not been shipped any edition of the book named 'The Cat in the Hat'. This should be done using universal quantification. The error I'm getting is - "ERROR: EXCEPT types integer and text cannot be matched" in reference to line 7 (EXCEPT (SELECT shipments.isbn).

CREATE VIEW no_cat_customers(customer_first_name, customer_last_name)
AS SELECT first_name, last_name
FROM customers
WHERE EXISTS (SELECT c_id
              FROM shipments
              WHERE customers.c_id = shipments.c_id 
              EXCEPT (SELECT shipments.isbn
                      FROM books, editions, shipments
                      WHERE books.book_id = editions.book_id AND
                        editions.isbn = shipments.isbn AND
                        title LIKE '%The Cat in the Hat%'));

I understand that this is hard to ask considering you don't have the database I'm using, but any help with this would be very much appreciated!

EDIT: I should add that there is two editions of 'The Cat in the Hat' in the database, both with different isbn's. So that has to be taken into account.

Upvotes: 0

Views: 150

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

Use explicit JOIN syntax instead of mixing it with real conditions in where clause. I believe that this should work:

CREATE VIEW no_cat_customers(customer_first_name, customer_last_name) AS 
SELECT first_name, last_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 
  FROM shipments s
  JOIN editions e ON s.isbn = e.isbn
  JOIN books b ON b.book_id = e.book_id
  WHERE b.title ILIKE '%The Cat in the Hat%'
  AND c.c_id = s.c_id
  )

If you have datatype errors, cast your columns to appropriate types before comparing them.

Upvotes: 2

Related Questions