asked on

Complicated sql query with 3 one to many tables.

Hola.

I have 3 tables.

1. Parent.
PID
PNAME

2. Child.
CID
PID_fk
CNAME

3. ChildAge
CAID
CID_fk
Age
Height

What I want is a select query that will return the age of the children in one column and their heights at these ages in the next columns. So something like:

Age Martin Steve Johan
1 0.4 0.5 0.6
2 0.6 0.7 0.7
3 1.0 1.2 1.15

So I dont mind building a query for each parent and I need to be able to handle upto 9 children so I dont need an exponentially complicated query.

HELP!

Asim

ASKER CERTIFIED SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sapnam

Are you going to use SQL or some tool like Report Builder finally to display this output.

asiminator

ASKER

thanks for the rapid response.

I still have a problem.... when I run the query it says age is not a single-group group function.

Any ideas?

Asim

seazodiac

yes, that's a syntax error, i fixed in the query below:

select age, max(decode(upper(name), 'MARTIN', height, 0)) "Martin",
max(decode(upper(name), 'STEVE', height, 0)) "Steve",
max(decode(upper(name), 'JOHAN', height, 0)) "Johan",
max(decode(upper(name), 'SMITH', height, 0)) "Smith",
max(decode(upper(name), 'JACK', height, 0)) "Jack",
max(decode(upper(name), 'MIKE', height, 0)) "Mike",
max(decode(upper(name), 'RICHARD', height, 0)) "Richard",
max(decode(upper(name), 'TERESA', height, 0)) "Teresa",
max(decode(upper(name), 'BONNIE', height, 0)) "Bonnie"
from (select A.cid, A.name, B.age, B.height
from child A, childage B
where A.cid = B.cid_fk
and upper(A.name) in ('MARTIN', 'STEVE', 'JOHAN', 'SMITH', 'JACK', 'MIKE', 'RICHARD', 'TERESA', 'BONNIE'))
group by age;

asiminator

ASKER

cool.... thanks.

Youre a champ!