CODEWITHSUNDEEP
sqlmysql
Baversjo
Baversjo

Reputation: 3716

sql inner join - multiple columns instead of rows

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

Upvotes: 1

Views: 1698

Answers (2)

coreyward
coreyward

Reputation: 80128

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.

Upvotes: 1

Erik
Erik

Reputation: 4105

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.

Upvotes: 0

Related Questions