Multiple rows for same id on Oracle 10

Consider two tables

TABLE PERSON

Code:

---

ID,name
1 foo1
2 foo2
3 foo3

---

and

TABLE VALUES

Code:

---

ID_PERS property value
1 | age      | 20
1 | address | street of flowers
2 | age      | 29
1 | city| PARIS
3 | address | street

---

My goal is to get results like:

Code:

---

1 | foo1 | 20    | street of flowers | PARIS
2 | foo2 | 29    | (null) |            (null)
3 | foo3  | (null) | street    |            (null)

---

That I arrived with queries like:

PHP Code:

---

SELECT ID,NAME,
(SELECT value from TABLE_VALUES WHERE ID_PERS=ID AND type='age'),
(SELECT value from TABLE_VALUES WHERE ID_PERS=ID AND type='address'),
(SELECT value from TABLE_VALUES WHERE ID_PERS=ID AND type='city')
FROM PERSON; 


---

It works when I have one 'age', one 'address' per PERSON. When there are multiple addresses, I have multiple rows returned and so the request fails. Is this normal?

Solution: add AND rownum = 1 to each subquery.

Problem: I only have one return value that I did not choose.

Question: How to return all values in repeating the People?
Like:

Code:

---

1 | foo1 | 20    | street of flowers | (null)
1 | foo1 | 20    | path of poplars | (null)
1 | foo1 | 20    | (null) | PARIS
1 | foo1 | 20    | (null) | MARSEILLE
2 | foo2 | 29    | (null) |            (null)
3 | foo3  | (null) | street    |            (null)

---

(example: 2 addresses and two cities)

Do what you want but your data model is bad, since because nothing that tells you what a city owns the street?

PHP Code:

---

SELECT ID,NAME,value, NULL,NULL
FROM PERSON
INNER JOIN TABLE_VALUES WHERE ID_PERS=ID AND type='age'
union ALL
SELECT ID,NAME,NULL,value,NULL
FROM PERSON
INNER JOIN TABLE_VALUES WHERE ID_PERS=ID AND type='address'
union ALL
SELECT ID,NAME,NULL,NULL,value
FROM PERSON
INNER JOIN TABLE_VALUES WHERE ID_PERS=ID AND type='city' 


---

Thank you for your reply.

Well OK for the data model, I adapted my case with an example not necessarily relevant (city, address). Imagine instead of multiple phones and emails ;-)

I adapted your example in my case (attention its not ON and WHERE) and I get what I thought, but I get 2 lines for couples (age, address = null, adr1 and 20 , null) instance and these values could be on the same line.

Maybe it is not possible in the same request to have these "conditions", and then a tip would allow me?

If your data model was not so bad, it would work alone. You have to get all this through a trick of style

PHP Code:

---

SELECT id, name, max(column1), max(column2), ...
FROM( [your current query] )
GROUP BY id, name 


---

... but frankly ugly anyway

In this case, add a property "name" in table "values" and pass with the table "person" if the person has several names, too, since it may already have several ages. To add a property to a single record, there is something called "ALTER TABLE".

The principle of multiple properties in itself is not an idiot but then it should ideally use another table "properties" listing the properties available each with an id and a label, we will use id instead of the label for the column "ownership" of the table values.