linking a date with another table date

[FB1]

Hi
MYSQL 5.2

I have two queries that pull dates out from different table

One date comes from ob_table
the other from HB_table

both dates look for an interval from now to -91 days
both queries work well.

I now need to join the queries together to get the HB date where there is an obsevation date the same.
but I am fast losing the will to live, could someone please advise on what I am not doing right.

the two queries are as follows

(select distinct p_anthropometry.didat, p_anthropometry.fk_oid from p_anthropometry
join(
select fk_oid,max(diDAT) as didat91 from p_anthropometry where
diwt is not null and didat > Date_sub(now(), INTERVAL 91 day)
and (p_anthropometry.prepost = 200100 or p_anthropometry.prepost is null)
group by fk_oid) q2
on p_anthropometry.fk_oid = q2.fk_oid
and p_anthropometry.didat = q2.didat91
where p_anthropometry.fk_oid=p.oid) as ob_date_91


,(select distinct p_haematology1.h1dat from p_haematology1
join(
select fk_oid,max(h1DAT) as h1dat91 from p_haematology1 where
h1hb is not null and h1dat > Date_sub(now(), INTERVAL 91 day)
and (p_haematology1.prepost = 200100 or p_haematology1.prepost is null)
group by fk_oid) q2
on p_haematology1.fk_oid = q2.fk_oid
and p_haematology1.h1dat = q2.h1dat91
where p_haematology1.fk_oid=p.oid) as hbdate_91

thank you all for all your time

 

[r937]

when you create a subquery like this --

( select fk_oid
       , max(diDAT) as didat91 
    from p_anthropometry 
   where diwt is not null 
     and didat > Date_sub(now(), INTERVAL 91 day)
     and ( p_anthropometry.prepost = 200100 
        or p_anthropometry.prepost is null )
  group 
      by fk_oid ) q2

then all the tables in that subquery are hidden outside the subquery, and you can no longer reference p_anthropometry.fk_oid, you must reference q2.fk_oid instead

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[FB1]

Thank you for your reply,
manage to work where I was going wrong

,(select distinct p_haematology1.h1dat from p_haematology1

join(
select p_haematology1.fk_oid,max(h1DAT) as h1dat91 from p_haematology1
join p_anthropometry
on p_anthropometry.didat=p_haematology1.h1dat
and p_anthropometry.fk_oid=p_haematology1.fk_oid
where
h1hb is not null and h1dat > Date_sub(now(), INTERVAL 91 day)
and (p_haematology1.prepost = 200100 or p_haematology1.prepost is null)
group by fk_oid) q2
on p_haematology1.fk_oid = q2.fk_oid
and p_haematology1.h1dat = q2.h1dat91