SQL conditional select and conditional where

Question

I have a problem creating a view or a select statement. the problem could be due to poor design so any suggestions could help.

I will try to simplify the design as follows:

• A service table, service.id and service.name
• A product table, product.id and product.name
• A package table, package.id and package.name
• an Item table, item.id and service.id (can be null) and product.id (can be null) and package.id (can be null)

an item table could contain either a service.id or product.id or package.id

So my question is how can I create a view/select to show the item table selecting the item.id and if item.service_id is not null then get that and the service.name, otherwise if the item.product_id is not null then get that and the product.name or if the item.package_id is not null get that and the package.name?

I tried a mixture of using sql case statement but I couldn't get it to work! I keep getting the item table with redundant incorrect rows. I expected to get something like:

Item ID	Service ID	Product ID	Package ID	Name
1	20	NULL	NULL	Hair Cut
2	NULL	30	NULL	Shampoo

Answer 1

Try this query please:

CREATE VIEW ItemDetails AS
SELECT
    item.id AS ItemID,
    item.service_id AS ServiceID,
    item.product_id AS ProductID,
    item.package_id AS PackageID,
    CASE
        WHEN item.service_id IS NOT NULL THEN service.name
        WHEN item.product_id IS NOT NULL THEN product.name
        WHEN item.package_id IS NOT NULL THEN package.name
    END AS Name
FROM
    item
LEFT JOIN service ON item.service_id = service.id
LEFT JOIN product ON item.product_id = product.id
LEFT JOIN package ON item.package_id = package.id;