Retrieving the Nth record from a one-to-many subrelation table

[walker3845]

Using Oracle 10g, I have a "Persons" table and a "Tasks" table, as shown below:

PERSONS Table
=================
PID, FName, LName
-------------------------------
1, John, Smith
2, Bob, Johnson
3, Jane, Walters

TASKS Table
=================
PID, Task#, Task
-------------------------------
1, 1, Change tires
1, 2, Cut grass
1, 3, Watch TV
2, 1, Mop floors
2, 2, Eat sandwich
3, 1, Divide and conquer

The tables are related through the PID field, and each person can have from 0 to 4 tasks assigned to them.

What I need is a query that for any Task # n (where n is 1 to 4), it will return the person's name and Task (or a null value if no Task # n is assigned for that person). For example, if I wanted to see the information for Task # 2, the query should return:

FName, LName, Task
-------------------------------
John, Smith, Cut grass
Bob, Johnsson, Eat sandwich
Jane, Walters, (null)


Any help is greatly appreciated

[debasisdas]

Kindly Post The Query That You Have Tried So Far.

[walker3845]

I have tried so many bits and pieces, but don't really have anything that has worked. I have tried CASE statements and RANK statements, but can't seem to get it to work. I am a C# developer, so whenever I have needed to "flatten" subrelation data, I have always been inclined to just get the various pieces of data I need separately and put them together in code. Now I am just looking for some more elegant (and faster) ways of retrieving the data.

I'll keep working on it and I'll post anything I have if I get close. Thanks for your help.

[walker3845]

OK, this is as close as I have gotten so far...

SELECT P.FName, P.LName, CASE WHEN (SELECT COUNT(*) FROM TASKS T WHERE T.PID = P.PID) >= 2 THEN 'Task Name' ELSE null END AS TaskName FROM PERSONS P ORDER BY P.PID

This is intended to return the data for all Persons for Task # 2. the CASE switches correctly, I just can't figure out how to select the actual Task instead of just inserting 'Task Name' (it does return the person's name and a (null) Task if the person has not been assigned a second task).

I tried a RANK function in place of the 'Task Name', but it always returns the second task for the first person (I think I understand why).

Again, I am mainly a developer with very little SQL experience, so I may be way off base with the direction I am going. Oracle may have some analytical functions that do just this thing.

Thanks again.

[surisetti]

select fname,lname,nvl(task,'null') from persons p, (select pid,task from tasks where task#=NUMBER OF TASK) tt where tt.pid(+)=p.pid

[walker3845]

Working perfectly. Thank you.

[Kumarswamy]

Hi,
Try this .......

select a.pid,a.fname,a.lname,nvl(b.task,'null') from persons a,(select pid,task from tasks where task#=2) b
where a.pid=b.pid(+);
Regards
Kumarswamy

Using Oracle 10g, I have a "Persons" table and a "Tasks" table, as shown below:

PERSONS Table
=================
PID, FName, LName
-------------------------------
1, John, Smith
2, Bob, Johnson
3, Jane, Walters

TASKS Table
=================
PID, Task#, Task
-------------------------------
1, 1, Change tires
1, 2, Cut grass
1, 3, Watch TV
2, 1, Mop floors
2, 2, Eat sandwich
3, 1, Divide and conquer

The tables are related through the PID field, and each person can have from 0 to 4 tasks assigned to them.

What I need is a query that for any Task # n (where n is 1 to 4), it will return the person's name and Task (or a null value if no Task # n is assigned for that person). For example, if I wanted to see the information for Task # 2, the query should return:

FName, LName, Task
-------------------------------
John, Smith, Cut grass
Bob, Johnsson, Eat sandwich
Jane, Walters, (null)


Any help is greatly appreciated