How can I classify the contents in 2 groups based on their values in SQL?

Question

I have a relational schema as follows:

INVENTORY(inventoryID,title,wholesale,markup,qtyinStock,discount) Primary-Key: inventoryID BOOK(coverType,inventoryID) Primary-Key: inventoryID Foreign-Key: inventoryID DVD(classification,ReleaseYear,StarRating) Primary-Key: inventoryID Foreign-Key: inventoryID

BOOK and DVD are basically the subtypes of Inventory. They are inheriting from INVENTORY. Now, I want to display all the items in the inventory as either a book or a DVD depending upon the items presence in the table. I did the following but I am not sure if this is right:

SELECT title FROM INVENTORY,BOOK,DVD WHERE INVENTORY.inventoryID = BOOK.inventoryID AND INVENTORY.inventoryID = DVD.inventoryID

This doesn't display the titles of the items though. How can I add that? Thanks!

SQLChao · Accepted Answer · 2016-06-01 15:47:54Z

1

Unless I read your question incorrectly the query is simple. Your example query pulls inventory that is both a DVD and Book.

SELECT title, CASE WHEN b.inventoryID IS NOT NULL THEN 'Book' WHEN d.inventoryID IS NOT NULL THEN 'DVD' END AS Category FROM Inventory i JOIN Book b on i.inventoryID = b.inventoryID JOIN DVD d on d.inventoryID = i.InventoryID

edited Jun 1, 2016 at 15:47

answered Jun 1, 2016 at 15:43

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

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

2 Comments

Harry Lewis Over a year ago

,So, I want to display whether an item in the inventory is a book or a DVD. I think your query just displays the title. That's it. I want to display the category as well (DVD/Book).

SQLChao Over a year ago

@HarryLewis I updated the answer. I only pulled the title because that is what you were pulling in your original example.

Sebastianb · Accepted Answer · 2016-06-01 15:36:33Z

0

With the AND in the WHERE clause you are trying to get the title of the inventory item that is both book AND dvd. Try changing the AND for and OR

answered Jun 1, 2016 at 15:36

Sebastianb

2,06024 silver badges31 bronze badges

1 Comment

Harry Lewis Over a year ago

So, I want to display whether an item in the inventory is a book or a DVD. I think your suggestion just displays the title. That's it. I want to display the category as well (DVD/Book).

Collectives™ on Stack Overflow

How can I classify the contents in 2 groups based on their values in SQL?

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related