Antrim Antrim - 2 months ago 7
SQL Question

Create a view based on three different tables

I have a table called Ingredients, with the following fields:

Ingredients - IngredientID (PK), ManufacturerID (FK), CategoryID (FK), BarcodeID (FK), SizeID (FK), Quality.

The way this table works is like this: each ingredient is going to be based on EITHER manufacturer, category, or barcode, only one of the three, and one is always required. So if a record has a ManufacturerID, CategoryID and BarcodeID will both be null, and vice-versa.

There are three other tables in my database:

Manufacturers - ManufacturerID (PK), Name

Categories - CategoryID (PK), Name

Barcodes - BarcodeID (PK), Name

I need a view that looks like this:

View - ViewID (PK), IngredientID (FK), Name, SizeID, Quality

Where ViewID will be the PK,
IngredientID will be the FK to the ingredient record,
Name will be the Name field coming from either the Manufacturers, Categories, or Barcodes table based on which of the three fields had a value in the Ingredients table,
and SizeID and Quality will come directly from the Ingredients table.

My knowledge of SQL is very limited and I would appreciate any guidance. Please let me know if my description of the problem lacks any vital information.

Edit: Updated the view to include the FK IngredientID, which could be useful.

Answer

Something like this should work using a CASE statement:

SELECT I.IngredientID, 
   CASE 
      WHEN I.ManufacturerID IS NOT NULL THEN M.Name 
      WHEN I.CategoryID  IS NOT NULL THEN C.Name 
      WHEN I.BarcodeID IS NOT NULL THEN B.Name 
   END Name,
   I.SizeID,
   I.Quality
FROM Ingredients I
   LEFT JOIN Manufacturers M ON I.ManufacturerId = M.ManufacturerId
   LEFT JOIN Categories C ON I.CategoryID = C.CategoryID
   LEFT JOIN Barcodes B ON I.BarcodeID = B.BarcodeID 

This uses the IngredientId as your Primary Key -- which is what I assumed you wanted. There is no need for another Primary Key. If you really just want an incremental Id, then use ROW_NUMBER -- but I don't see why it would be needed:

SELECT ROW_NUMBER() OVER (ORDER BY I.IngredientID), ...