0

I have the following 2 tables:

Parameters table: ID, EntityType, ParamName, ParamType

Entity table: ID, Type, Name, ParamID, StringValue, NumberValue, DateValue

  • Entity.ParamID is linked to Parameters.ID
  • Entity.Type is linked to Parameters.EntityType
  • StringValue, NumberValue, DateValue contains data based on Parameters.Type (1,2,3)

the query result should contain: 

Entity.ID, Entity.Name, Parameters.ParamName1, Parameters.ParamName2... Parameters.ParamNameX

The content of ParamNameX is as the above correlation. How is it possible to turn the parameters names into columns and their values into data of those columns? I don't even know where to begin.

Explanation for the above: for example entity X can be entitytype 1 and entitytype 2. parameters table contains paramname for both type 1 and 2 but I need to get (for example) only entity type 1's paramname.

Amos
  • 1,321
  • 2
  • 23
  • 44
  • I suppose you need a dynamic pivot: http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot – Multisync Nov 08 '14 at 14:05
  • Please read my additional info in my question. My need is a bit different and I'm lost... – Amos Nov 08 '14 at 14:33
  • It's not a problem to join two tables and retrieve rows containing all the required parameters. But you need to convert the rows into columns and you don't know how many columns will be there. It's called a dynamic pivot. – Multisync Nov 08 '14 at 14:42

1 Answers1

0

What you are trying to archive is a EAV (Entity Attribute Value) Model.

But the way you set up your tables is just wrong.

You should have a table per type. So entity_string, entity_number, entity_date and a main table entity which holds the id and some general stuff like create_time, update_time and so on.

Look at magento and how they set up their tables. Like this it is much easier to ask for your data and organize it.

Eydamos
  • 562
  • 4
  • 16
  • I edited my question. The reason I did it this way is because the entity can have multiple entity-types so the parameters table have ALL the available parameters for all entity types but I need to get only the relevant parameters for the asked entity type. – Amos Nov 08 '14 at 14:29
  • For example entity X can be entitytype 1 and entitytype 2. parameters table contains paramname for both type 1 and 2 but now I need to get only entity type 1's paramname. – Amos Nov 08 '14 at 14:31