CODEWITHSUNDEEP
sqlsql-server
Roelant M
Roelant M

Reputation: 1706

sql query challenge for consolidating data

I'm having some trouble writing the correct query. It would be appreciated if someone smarter ;) then me can help me.

It feels like it is easy, but I am just missing something....

so I have 2 tables;

CarColors and CarColorTranslations.

locally I have this for CarColors (parent is CarDetail)

enter image description here

Currently in this table we have the countrylocale where it belongs. Like the red box: (Id 5005 and 8006) are both related to cardetail 1 and only have a different name (other language of course)

But this is not the correct model. I need to get rid of CountryLocaleId and Name and put this in the CarColorTranslations table.

So first I had written this:

Insert into CarColorTranslations (Created, CreatedBy, Changed, ChangedBy, CarDetailId, CarColorId, CountryLocaleId, Name)
  select GETDATE(),'migrations', GETDATE(), 'migrations', cc.CarDetailId, cc.Id, cc.CountryLocaleId, Name from CarColors cc

this does write the rows to the other table; but I forgot one thing; i need to consolidate the data in carcolors; meaning; Id 5005 and 8006 need to be one; so i need to get rid of 8006 (or the other). But at this point i will loose the parent from one record in CarColorTranslations..

CarColorTranslations has these few properties

CarDetailId (FK cardetail)
CarColorId (FK CarColor)
CountryLocaleId (FK countryLocale)
Name (Real translated value)

for consolidating the data and writing the queries to CarColortranslations; the combined key to match is CarDetailId, HexColorCode, ColorCombinationId, Price

as you see; the next record (9005), is a different color -for the same car- and does/should not match the other one.

Hope that it is clear.

Thnx!

Upvotes: 1

Views: 54

Answers (1)

Chris Mack
Chris Mack

Reputation: 5208

Firstly, I will mention that I don't think the CarDetailId column is necessary in CarColorTranslations, as this is already available in its parent of CarColors.

For the INSERT, I think something like this is what you'd be looking for:

Insert into CarColorTranslations
(
    Created
    , CreatedBy
    , Changed
    , ChangedBy
    , CarDetailId
    , CarColorId
    , CountryLocaleId
    , Name
)

select
    GETDATE()
    ,'migrations'
    , GETDATE()
    , 'migrations'
    , cc.CarDetailId
    , MIN(cc.Id) OVER (PARTITION BY CarDetailId, HexColorCode, ColorCombinationId, Price)
    , cc.CountryLocaleId
    , Name
from CarColors cc

You could then look into doing something like a LEFT JOIN on CarColorId to DELETE CarColors records that are no longer in use.

Upvotes: 1

Related Questions