Hello
Select SQL gives me result with multiple repeated records. how to avoid the repeated records?
Tables (jorders is parent table while others are childs tables)
Jorders (Job Orders) fields (idjo, jono, descript, jodate)
Jomatcost (JO Material cost) fields (idjo, material, qty, stdcost)
Jompcost (JO Manpower cost) fields (idjo, employname, reghrs, reghrrate)
Joeqpcost (JO Equipment cost) fields (idjo, equipment, equipcost)
To display detail cost of each Job Order, I need the result of these tables group by jono, material, employname, equipment with total cost.
The following is the command which I use to get the required result but it gives repeated records,
SELECT Jorders.jno, Jomatcost.material, Jompcost.employname,;
Joeqpcost.equipname, SUM(Jomatcost.qty*Jomatcost.stdcost) AS matcost,;
sum(Jompcost.reghrs* Jompcost.reghrrate) as mpcost,;
SUM(Joeqpcost.equipcost) AS eqpcost, SUM(Jomatcost.qty) AS qty;
FROM ;
main!jorders ;
LEFT OUTER JOIN main!jomatcost ;
ON Jorders.idjo = Jomatcost.idjo ;
LEFT OUTER JOIN main!joeqpcost ;
ON Jorders.idjo = Joeqpcost.idjo ;
LEFT OUTER JOIN main!jompcost ;
ON Jorders.idjo = Jompcost.idjo;
GROUP BY Jorders.jno, Jomatcost.material, Jompcost.employname,;
Joeqpcost.equipname
Any alternative ?
Regards,