union - combining two select statements with GROUP and JOIN mysql -


hope can help.

i have 2 mysql select statements each work, need combine them.

first:

select $shares.*, $shares_company.name name, sum($shares.number) totalnumber, max(date) lasttransdate, $shares.timestamp lastupdated $shares  inner join $shares_company $shares_company.id = $shares.company_id group   $shares.company_id 

second:

select $shares.price lastprice  $shares ($shares.company_id,$shares.date) in  (select $shares.company_id, max($shares.date) $shares group $shares.company_id) 

i tried union , union all, , sorts of other combinations.

i stuck!

enter image description here

update:

well, that's how far got. returns correct info except need sum of 'number' column , don't know insert query. query insert is:

select sum($shares.number) totalnumber $shares  group $shares.company_id 

the query have is:

select $shares.price lastprice, $shares.date lasttransdate,  $shares.company_id, $shares.id, $shares.timestamp lastupdated, $shares_company.name $shares   inner join $shares_company on $shares_company.id = $shares.company_id  ($shares.company_id,$shares.date) in  (select $shares.company_id, max($shares.date) $shares group $shares.company_id) 

thanks help.

if understand correctly, want lastprice result within first query. in case, i'd try this:

select   $shares.*,   $shares_company.name name,   sum($shares.number) totalnumber,   max(date) lasttransdate,   $shares.timestamp lastupdated $shares,   ( select $s.price     $shares $s     $s.company_id = $shares.company_id     order $s.date desc     limit 1   ) lastprice   inner join $shares_company     on $shares_company.id = $shares.company_id group $shares.company_id 

(note: untested, reasoning should clear: create inner query select last share price given row, return column).


Comments