Joining a table of properties as columns and values?

Question

CREATE TABLE person_properties (
  person_id INT,  
  key TEXT,
  value TEXT,
  PRIMARY KEY (person_id, key)
);

CREATE TABLE persons (
  id SERIAL PRIMARY KEY
);

I've done this:

INSERT INTO persons DEFAULT_VALUES;  -- suppose id is 1
INSERT INTO person_properties (person_id, key, value) VALUES (1, 'age', '30')
INSERT INTO person_properties (person_id, key, value) VALUES (1, 'weight', '20lb')

I'd like to select persons with age and weight as columns, with the values from person_properties (or NULL if it doesn't exist). How can I do this?

score 1 · Accepted Answer · answered Jul 20 '14 at 07:44

1

SELECT * FROM persons
LEFT OUTER JOIN person_properties p1 ON persons.person_id = p1.person_id AND p1.key = 'age'
LEFT OUTER JOIN person_properties p2 ON persons.person_id = p2.person_id AND p2.key = 'weight'

answered Jul 20 '14 at 07:44

schlonzo

1,409
13
16

Alexis_user · Answer 2 · 2014-07-20T12:32:28.420

-1

Your schema is wrong.

key is reserved & you can't have 2 primary keys in a unique table.

CREATE TABLE person_properties (
  person_id INT,  
  cle TEXT,
  value TEXT,
  PRIMARY KEY (person_id)
);

CREATE TABLE persons (
  id SERIAL PRIMARY KEY
);

and

You can't to have twice same primary key ...

INSERT INTO person_properties (person_id, cle, value) VALUES (**1**, 'age', '30');
INSERT INTO person_properties (person_id, cle, value) VALUES (**1**, 'weight', '20lb');

If you must have 2 lines with the same key, it's not good.

I advice you to redo//rethink your schema.

EDIT :

CREATE TABLE person_properties (
  person_id INT NOT NULL AUTO_INCREMENT,  
  pkey VARCHAR(10),
  value TEXT,
  PRIMARY KEY (person_id, pkey)
);

CREATE TABLE persons (
  id SERIAL PRIMARY KEY
);

INSERT INTO person_properties (pkey, value) VALUES ('age', '30');
INSERT INTO person_properties (pkey, value) VALUES ('weight', '20lb');

edited Jul 20 '14 at 12:32

answered Jul 20 '14 at 09:13

Alexis_user

427
5
14

You *can* have multiple columns in a primary key. Postgresql see http://www.postgresql.org/docs/current/static/sql-createtable.html, Mysql see http://stackoverflow.com/questions/2642778/multi-column-primary-key-in-mysql-5 – harmic Jul 20 '14 at 09:27
These are the same enitity, not the same key. It's fine, just difficult to work with. (key IS a reserved word though!) – Strawberry Jul 20 '14 at 09:57
@harmic : Yes but he can NOT use key/cle as primary key !!! His type is TEXT and a primary key HAS TO have a fixed length. And his INSERT INTO queries are though false... – Alexis_user Jul 20 '14 at 12:29
1

If you mean it is not a good design, well sure that is arguable. Using EAV is rarely a good idea. But there is no reason why a primary key cannot have a TEXT column as part of it. OPs schema does work - see http://sqlfiddle.com/#!15/7330e/1/0 – harmic Jul 20 '14 at 23:51
I misread... Of course it works with PostgreSQL but not with mysql :s – Alexis_user Jul 21 '14 at 07:02

Joining a table of properties as columns and values?

2 Answers2