Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

From the following tables:

store(store_id, ...) {1 ..., 2 ...} two stores
film(film_id, ...)
inventory(inventory_id, film_id, store_id)
rental(rental_id, rental_date, inventory_id, return_date, ...)
category(category_id, name)
film_category(film_id, category_id)
film(film_id, rental_duration, rental_rate, ...)
customer(customer_id, store_id, ...)

Can I have a query result on a table just like the following?

CategoryName | "Store 1: available items" | "Store 1: unavaliable items" | "Store 2: avaliable items" | "Store 2: unavaliable items"

Notice that the store fields in the result table are in separate columns not in the same columns !!

I am not looking for the result query, I am looking for how to make them side by side store 1, store 2.

share|improve this question

1 Answer

up vote 1 down vote

I don't have the energy to deal with all the joins through all your tables, but the general idea would be to join to the store table twice and check the results.

 SELECT f.film_id,
   CASE when store1.store_id is not null then 'Item exists in store 1' else 'Item missing from store 1' END as 'Store 1 Status'
 FROM film f
 LEFT OUTER JOIN store store1 on f.film_id = store1.film_id AND store1.store_id = 1
 LEFT OUTER JOIN store store2 on f.film_id = store2.film_id AND store2.store_id = 2

This doesn't scale well if you had 100 stores, but for two it wouldn't be evil.

Hope that helps.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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