Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 23-11-2009
Member
 
Join Date: Aug 2009
Posts: 63
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)
Reply With Quote
  #2  
Old 23-11-2009
Member
 
Join Date: Nov 2008
Posts: 1,192
Re: Multiple rows for same id on Oracle 10

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,valueNULL,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' 
Reply With Quote
  #3  
Old 23-11-2009
Member
 
Join Date: Aug 2009
Posts: 63
Re: Multiple rows for same id on Oracle 10

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?
Reply With Quote
  #4  
Old 23-11-2009
Member
 
Join Date: May 2008
Posts: 685
Re: Multiple rows for same id on Oracle 10

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 idnamemax(column1), max(column2), ...
FROM( [your current query] )
GROUP BY idname 
... but frankly ugly anyway
Reply With Quote
  #5  
Old 23-11-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: Multiple rows for same id on Oracle 10

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Multiple rows for same id on Oracle 10"
Thread Thread Starter Forum Replies Last Post
Help me transposing multiple rows into one column with row labeling in Excel RYAN$ MS Office Support 2 17-01-2012 06:31 PM
How to Insert Multiple Rows At Once in PHP Zeverto Software Development 3 22-09-2009 03:30 PM
Merging duplicate names not rows copy rows into new workbook each time VBA Excel 2003 awkwardsmile Software Development 2 09-07-2009 08:57 AM
Multiple rows of tabs in Firefox Mr.ken Technology & Internet 3 25-04-2009 11:49 AM


All times are GMT +5.5. The time now is 04:31 PM.