Avatar of EffinGood
EffinGood
Flag for United States of America asked on

SQL query many-to-1

Hi all,

I have two tables, tbl1 and tbl2.  Tbl2 is a collection of ids and colors, for example:
colorId | color
1 | Red
2 | Blue
3 |Green
etc.

tbl1 is a table of items, and associated with each item id is a single color id.  For example:

itemId | name | colorId
1 | shirt | 1
2 | cake | 3
3 | umbrella | 2
etc.

Both tables are already populated with data.  Currently, this is a 1 to 1 relationship. Each item is only available in 1 color.  I need to set it up so that each item is available in multiple colors.  I have built tbl3 to allow a 1-to-many relationship going forward:

colorsCollectionId | colorId
1 | 1
1 | 2
2 | 3
2 | 1
etc.

Here's my question: how can I write a SQL query that handles the existing data, and migrates the current values from the colorId field tbl1 to tbl3, and replaces them in tbl1 with a colorsCollectionId?

Thanks for your help!
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
vasto

8/22/2022 - Mon
dsacker

Would you be willing to remove the colorID from your tbl2, and change your tbl3 just a little bit to accommodate a many-to-many relationship:

TBL1

itemId | name
1 | shirt
2 | cake
3 | umbrella
etc.

TBL2
colorId | color
1 | Red
2 | Blue
3 |Green
etc

TBL3
itemID | colorID
1 | 1
2 | 3
3 | 2
4 | 2
5 | 2
etc

If you need to put colors in a collection, I suggest simply adding a collectionID to your colorID table.
dsacker

This give you the power of versatile querying on collectionID (if you want to add it), what item has what colors, what color is in what items, or any of that by collectionID. This keeps the relationships in their proper separation. Hope this helps.
ASKER CERTIFIED SOLUTION
vasto

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ThomasMcA2

You may not need a 3rd table. Just add new records to tbl1 with an existing itemID and a new colorID (for that itemID.) Regardless of whether you use my way or your way, existing code will have to be changed to process the multiple colors. My way, however, would be much easier to implement. Note that if tbl1 is uniquely keyed on itemID, that will have to be changed.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
vasto

Thanks EffinGood! my answer actually is not the first correct answer ( slow typing ) . dsacker suggested the same changes and he/she posted it first. If  the last select statement in my post is helpful I guess it will be fair to share the points between me and dsacker. If this statement is not helpful all the point should go to dsacker . You can reopen this question and reassign the points. Thanks again.