sql inner join - multiple columns instead of rows

Question

My db structure:

t_data_fields
id | field_name | field_value

t_data
id | fields_id | name |

Each of the rows in t_data have a number of rows associated with it in the t_data_fields table. Instead of joining data width data_fields and get multiple rows for each data entry, I was wondering if it's possible to append the extra field_name and field_value to the original t_data row.

So, instead of:

id  fields_id name   field_name field_value
2   1         row2   color      black
2   1         row2   brand      DG
2   1         row2   type       shirt

I want the result like this:

id  fields_id name   field_name field_value field_name field_value field_name field_value
2   1         row2   color      black       brand      DG          type       shirt

I could do the first version, I'm just curious to see if it is possible to get the result set like I would prefer

Are the ID's correct in your example for the data in your t_data_field table? Are you using composite keys? — frogstarr78
– frogstarr78, Commented Nov 6, 2010 at 22:53

coreyward · Accepted Answer · 2010-11-06 22:39:04Z

1

You can JOIN a table repeatedly using a different alias each time, but it's not going to scale well, and it isn't flexible.

Judging by what you appear to be doing here, it would make a lot more sense for you to define your models/schema a bit more clearly so that any given product has particular attributes called color, brand, size, type, etc. and allow them to be NULL. Then you have a really clean row with all the data you need and you're using MySQL as-intended. Don't worry about having a lot of columns: MySQL is fine with having 50 columns, despite it being less human-friendly (caveat: there is a row-length limit so watch out if you have a lot of long varchars).

The other option, assuming you need arbitrary attributes, would be to store the data encoded in a TEXT field in the database in a single column (I recommend JSON if you go this route). It's a lot more difficult to perform database operations on the data this way, though, so there's definitely a tradeoff.

answered Nov 6, 2010 at 22:39

coreyward

80.5k21 gold badges148 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Baversjo Over a year ago

I need arbitrary attributes. Thank you for your answer, makes perfectly sense. I might use JSON for this in the future as I don't need to perform operations on this "extra" data.

Erik · Accepted Answer · 2010-11-06 22:34:11Z

0

Hmm, why? But no, can't do. If you want to present the result as in the last version, you do that in your programming behind the SQL-query.

answered Nov 6, 2010 at 22:34

Erik

4,1052 gold badges30 silver badges20 bronze badges

1 Comment

Baversjo Over a year ago

The reason would be less data over the wire. Yeah that's what I'm doing :)

Collectives™ on Stack Overflow

sql inner join - multiple columns instead of rows

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related