JOIN AND GROUP BY

[Terris]

I have two tables
INTRAN with two fields that apply fmjobno and fqty

JOMAST with several fields I require, for this example lets use fjobno, fordqty, ford_dt

there is a 1 to many relationship where jomast with be 1 record INTRAN will be many entries for the same jobno

result would be everything from JOMAST and a total of fqty from INTRAN where fjobno = fmjobno
Cannot seem to get the select, join and group by to work properly and could use a hand

 

[rt63]

If in jomast, you could have multiple records for same fjobno, then this should work


select jomast.fjobno , fordqty , ford_dt , sum_fqty
from jomast ,
(select fjobno, sum(fqty) as sum_fqty
from jomast , intran
where fjobno = fmjobno
group by fjobno) out_1
where jomast.fjobno = out_1.fjobno


Otherwise if each record in jomast has a unique fjobno, then a more efficient query will be


select fjobno, min(fordtqty), min(ford_dt) , sum(fqty)
from jomast , intran
where fjobno = fmjobno
group by fjobno


RT