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!

dsacker

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

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.