Creating a complicated view SQL Server-CodePudding

I would like to create a view so that I can search for a product id and get information about it's PM group. I'm a novice at best with SQL and struggle when these problems come up.

This query was working until I was asked to add another level of complexity to it.

    Select gct.user_def_num1, gct.user_def_num2, gct.user_def_num3
    From product p
    Join part_model pm on pm.part_id = p.part_id
    Join model m on m.model_id = pm.model_id
    Join global_code_table gct on gct.code_value = m.model_group

Each Model includes multiple Parts which are linked together with the part_model table. Previously we set the PM Group at the Model level, but now we want to be able to override it, if it's set at the part level on part.user_def2

I've been messing around with this and have a query that will return the the pm group tied to the part if its set, otherwise return the pm group tied to the model. I have been unable to figure out how to use the result to get the values I want from the global_code_table.

    SELECT CASE WHEN part.user_def2 IS NULL THEN model.model_group ELSE part.user_def2 END as pm_group
    From product
    Join part_model on part_model.part_id = product.part_id
    Join part on part.part_id = product.part_id
    Join model on model.model_id = part_model.model_id

I'm hoping I can create a view that when queried for the product_id will return the following.

pm_group (part.user_def2 or if that is null then model.model_group), global_code_table.user_def_num1 (where global_code_table.code_value = pm_group), global_code_table.user_def_num3 (where global_code_table.code_value = pm_group), global_code_table.user_def_num3 (where global_code_table.code_value = pm_group)

Any assistance is greatly appreciated!

PRODUCT TABLE

product_id  part_id
1           P1
2           P2

MODEL TABLE

model_id    model_group
M1          G1

PART-MODEL TABLE (links parts to a model)

part_id     model_id
P1          M1
P2          M1

PART TABLE (user_def2 will contain same group names as model.model_group)

part_id     user_def2
P1          NULL
P2          G2

GLOBAL_CODE_TABLE

code_value  user_def_num1  user_def_num2  user_def_num3
G1          100.00          75.00          50.00
G2          200.00         150.00         100.00

Expected Output (Query for pruduct_id = 1)

user_def_num1  user_def_num2  user_def_num3
100.00          75.00          50.00

Expected Output (Query for pruduct_id = 2)

user_def_num1  user_def_num2  user_def_num3
200.00         150.00         100.00

Both products have the same model_group, but product 2 has a model group set on the part table so it should be used instead of the model.model_group.

I also need this whole thing to be a view. The reason for this is because of the business software we're using. all the SQL has to be converted into an XML format which only allows for simple queries. The software company rep suggested creating views as a way to run more complex SQL via a simplified XML query.

CodePudding user response：

After a lot of searching the internet yesterday I was able to solve this myself. Here is the solution I came up with for anyone else who might need it.

The missing part I needed was the COALESCE() function. It will return the first non-null value. Since I want to try and join on the part table if there's a value in part.user_def2 I put that column first. If it's NULL it will use model.model_group. If both are NULL it just returns no rows.

For my situation I still need to create a view out of this, but that shouldn't be too hard to figure out.

    SELECT 
      product_id, 
      gct.code_value AS pm_group, 
      gct.user_def_num1 AS contract_price, 
      gct.user_def_num2 AS pm_price, 
      gct.user_def_num3 AS care_kit_price 
    FROM 
      product 
      JOIN part_model ON part_model.part_id = product.part_id 
      JOIN part ON part.part_id = product.part_id 
      JOIN model ON model.model_id = part_model.model_id 
      JOIN global_code_table gct ON coalesce(
        part.user_def2, model.model_group
      ) = gct.code_value 
    WHERE 
      product.product_id = 1