SQL Server Select Query With Join/Sub Query On Same Table To Create A Master Field Value

This is a weird query, and I’m not sure if what I want to do is even possible (Efficiently anyway) as it’s a bit of a weird situation.

Let’s say I have the following data called ProductTable. They are product variants with one product being the master product (Default). They are grouped by ProductKey.

VariantId, ProductKey, Name, Description, Values, Master
1, 1, Name One, Description One, {large json chunk}, true
2, 1, Name Two, Description Two, {small json chunk}, false
3, 1, Name Three, Description Three, {small json chunk}, false
4, 2, Name Four, Description Four, {small json chunk}, false
5, 2, Name Five, Description Five, {small json chunk}, true

I want to be able to query for non master variants, but append the [Values] column of the master product to the query as a column called [MasterValues]. So if I queried simply for all non master products, something like

SELECT * 
FROM ProductTable 
WHERE [Master] = 0

I would get the following result:

VariantId, ProductKey, Name, Description, Values, Master
2, 1, Name Two, Description Two, {small json chunk}, false
3, 1, Name Three, Description Three, {small json chunk}, false
4, 2, Name Four, Description Four, {small json chunk}, false

However, I would like to have the following column [MasterValues] that is populated by the [values] column of the master product for that productGroup:

VariantId, ProductKey, Name, Description, Values, Master, MasterValues
2, 1, Name Two, Description Two, {small json chunk}, false, {large json chunk from variant id 1}
3, 1, Name Three, Description Three, {small json chunk}, false, {large json chunk from variant id 1}
4, 2, Name Four, Description Four, {small json chunk}, false, {large json chunk from variant id 2}

I realise this is a bit weird, but is it possible? I thought maybe a Join of some sort on the same table?

Answer

Assuming the relationship between variant to master products is many to one, you can get away with a self-join here. Basically, we’re going to select all the variant products from the ProductTable table, then join to the (same) ProductTable to find the master product.

Here’s an example:

SELECT 
    pa.* --this is not a best practice, ideally you should list out all the selected columns individually
    , pb.Values
FROM ProductTable pa --'pa' is the variant, while 'pb' is the master
INNER JOIN ProductTable pb --you may wish to do a left join here if there is not always a master product for a given variant
ON pb.ProductKey = pa.ProductKey
    AND pb.Master = 1
WHERE pa.Master = 0

Advertisement

Answer