0

table relationship

table relationship

Hi all I need help with how to enter and pull data from a simple many-to-many relationship table in MYSQL as I’m totally lost and about to go with a SET datatype but I know many-to-many is the correct way to do this. I’ve included a picture of the table layout to help you better understand. I have 3 tables (shirt, shirt2color, color). The shirt table has 2 columns (shirt_id, shirt), the shirt2color (mapping table) has 2 columns (shirt_id, color_id), and the color table has 2 columns (color_id, color).

I have already created the tables and relationships but how do I enter and pull data without manually mapping? Isn’t there an INSERT statement where I could add the t-shirt and tell the database it’s available colors in one statement and it would add “t-shirt” and “red, green, and blue” to the database as well as populate the mapping table automatically? Using the examples in the photo as a guide: (p.s. I imagine that’s how the example relationship data gets stored in the mapping table, if not please let me know)

  1. What would the SQL statement be to INSERT the t-shirt (which is available in red, green, and blue)?
  2. What would the SQL statement be to SELECT and list all shirts that are available in Red?
  3. How do you handle duplicate colors on shirt entries? For example if I add another shirt that comes in red I don’t want to add the color red again with color_id #4, I’d want to use color_id #1.

I know these are probably total newbie questions but I can’t find the answer to these questions anywhere online over the last few weeks. Thanks for any help you guys can offer.

sticky bit
  • 36,626
  • 12
  • 31
  • 42
Robert
  • 35
  • 5
  • What language you are using? I see a Set datatype, are you using JPA? Or you simply want to achieve this by native SQL? – Jacob Jul 19 '18 at 00:59
  • Ultimately I will be using PHP on my site but I wanted to see if there is a straight SQL statement such as there is when requesting data from inner joined tables – Robert Jul 19 '18 at 20:35

1 Answers1

0

1)

You can't - you need to write separate insert statements for each table.

2) 

SELECT shirt.shirt_id, shirt.shirt FROM shirt
INNER JOIN shirt2color ON shirt.shirt_id = shirt2color.shirt_id
INNER JOIN color ON shirt2color.color_id = color.color_id AND color.color = 'Red'

3)

You could add a UNIQUE constraint to the color column of the color table, this will prevent a duplicate entry from being created. When you want to insert a new item you should first check to see if the color already exists in the table and use it in the shirt2color table if it does.

SBFrancies
  • 3,987
  • 2
  • 14
  • 37
  • for #1, can I put the INSERT's into a BEGIN,COMMIT so it does it all at once? For #3, so will I need to write a check routine in PHP since there is no built-in mechanism in SQL to do this? – Robert Jul 19 '18 at 20:38
  • Yes, you can do it all in one transaction - if that's what you mean, just not one statement - see this answer https://stackoverflow.com/a/5178713/8532748 – SBFrancies Jul 20 '18 at 07:40