how to join two select?

[ziziak]

I need to create view from two select statements, need to join them.
1. select A.id,A.projectId,A.lastUpdated,A.oldValue from (
select bug.id as id
,bug.project_id as projectId
,bug.last_updated as lastUpdated
,his.old_value as oldValue
from table1 bug
left join history his on bug.id = his.bug_id
where .....
order by his.date_modified
limit 1) A
2. select B.id,B.projectId,B.lastUpdated,B.newValue from (
select bug.id as id
,bug.project_id as projectId
,bug.last_updated as lastUpdated
,his.new_value as newValue
from table1 bug
left join history his on bug.id = his.bug_id
where .....
order by his.date_modified desc
limit 1) B

I expect result as:
Bug.id,project.id,lastUpdate,oldValue,newValue
how can I join this two select statements?
thx

 

[r937]

select bug.id         
     , bug.project_id   as projectId
     , bug.last_updated as lastUpdated
     , hisold.old_value as oldValue
     , hisnew.new_value as newValue
  from table1 bug 
left 
  join history [b]hisold[/b]
    on hisold.bug_id = bug.id
   and hisold.date_modified = 
       ( select [b]min[/b](date_modified)
           from history
          where bug_id = bug.id )
left 
  join history [b]hisnew[/b]
    on hisnew.bug_id = bug.id
   and hisnew.date_modified = 
       ( select [b]max[/b](date_modified)
           from history
          where bug_id = bug.id )  
 where .....

r937.com | rudy.ca