Linking relatons

[nikol]

I have a table with columns as:
ID Hold_id Relationship age
789 123 01 67
890 123 02 30
897 123 06 29
234 134 01 40
245 134 07 12
246 134 06 17

Id=Clients_id, Relationship 01-father
02-friend
06-Son
07-Daughter
I want the o/p as
789 123 06 29
234 134 07 12
246 134 06 17
Ages of Son's & Daughter with thier parents IDs..

 

[amrita418]

What is the criteria for picking the ID field?

Regards,
AA

 

[nikol]

There is a table "household" Which has ID,hold_id,& relationship columns. So, I have to find the IDs who have children(If yes,what ages) & I got ages by linking other tables.Is there any way??

 

[cognoscognos]

Nikol,

Your query (or the o/p shown) is not very clear to me.
I understand ur 'household' table (ID,hold_id,& relationship columns). Can you give a sample of ur other table which contains age?

 

[nikol]

There's a table with ID & val_date columns.
This ID is same as "Household "table ID &
datediff(yyyy,val_date,getdate()) as age.

I tried this query
SELECT household.client_id,
household.relationship,
datediff(yyyy,val_date,getdate()) as age
FROM (da_answer INNER JOIN
household_relationships ON
da_answer.client_id=household_relationships.client_id)
INNER JOIN
secunsec ON da_answer.uid=secunsec.uid

I got "val_date"in datediff formaula from secunsec table.

 

[amrita418]

I am still not clear about your requirement (I do not know how you pick a specific ID?) Are the ID values in your sample output right? If yes, what is the logic to pick them.

Other than that, try something like this:

select      a.id, 
            a.hold_id, 
            a.relationship, 
            datediff(year, b.val_date, getdate())
from        household a 
            inner join secunsec b
            on (a.id = b.id)
where       a.relationship in ('06', '07')

PS: Assumption was that the id in household table matches id in secunsec table.
Regards,
AA

 

[nikol]

Hi Anita,
Thanks for ur replies. I'm back again. I'm using the following query. I hope it will help u to understand what I'm doing & What I want.

SELECT da_answer.client_id, household_relationships.client_id,household_id,
picklist_values.name, secunsec.uid, household_relationships.relationship
FROM ((da_answer INNER JOIN
household_relationships ON da_answer.client_id=household_relationships.client_id) INNER JOIN secunsec ON
da_answer.uid=secunsec.uid) INNER JOIN picklist_values
ON household_relationships.relationship=picklist_values.uid