0

I have two tables, call them Parents and Properties

Each parent has various columns and a id field. Each property has a key, a value column and a parent_id column that is a foreign key to the parent table. The key and the value can be any string. Each parent has zero or more properties.

How can I select on the same row the parent (and its columns) and all the linked properties? Thanks

EDIT Its is ok for me to select all the joined values as a csv-like string, for example "key1|val1|key2|val2|key2|val2|".

Example of wanted output

|parent_id|parent_name|prop_key1|prop_val1|prop_key2|prop_val2|prop_key3|prop_val3|
|---------------------------------------------------------------------------------|
|1        |foo        |key1     |val1     |key2     |val2     |         |         |
|2        |foo        |key1     |val1     |key2     |val2     |key3     |val3     |
|3        |foo        |         |         |         |         |         |         |
|4        |foo        |key1     |val1     |key2     |val2     |key3     |val3     |
|4        |foo        |key1     |val1     |         |         |         |         |

SAMPLE DATA:

http://sqlfiddle.com/#!9/b770e

pistacchio
  • 56,889
  • 107
  • 278
  • 420
  • 6
    If you are going to ask a question like this, you should provide sample data, desired results, and a query that you have tried. You have pretty good reputation on this site, so I'm surprised that you don't know how to write a good question. – Gordon Linoff Apr 07 '16 at 11:13
  • 1
    Pivoting data can be implemented differently depending on the dbms. So which dbms are you using? – vc 74 Apr 07 '16 at 11:17
  • @GordonLinoff Please see the edit for sample of wanted output and sample schema / data – pistacchio Apr 07 '16 at 11:21
  • @vc74 MySql. In the edit the sample data – pistacchio Apr 07 '16 at 11:22
  • The perils of the EAV model. – Tom H Apr 07 '16 at 11:22
  • Is there a limit how many properties a parent can have? Looking at the sample output, it seems like you expect up to 3... – S.B. Apr 07 '16 at 11:24
  • @S.B. As stated in the question, Each parent has zero or more properties. For "or more" i mean infinite – pistacchio Apr 07 '16 at 11:29
  • 1
    @pistacchio well, then the answer would be to use GROUP_CONCAT, as outlined in the linked answer. – S.B. Apr 07 '16 at 11:31

0 Answers0