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.
Something like this should work using a
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), ...