my sql

[sangeetha.ar]

hi,

SELECT Sum(acpt_qty),mat_code1 FROM view_mrir_esd GROUP BY mat_code1

SELECT Sum(net_qty),mat_code1 FROM view_bom_total GROUP BY mat_code1

these 2 queries are giving the actual report, but when combining these queries like

SELECT DISTINCT view_bom_total.mat_code1,Sum(net_qty),Sum(view_mrir_esd.acpt_qty) FROM view_bom_total inner join
view_mrir_esd ON view_bom_total.mat_id=view_mrir_esd.mat_id GROUP BY view_bom_total.mat_code1

giving the wrong acpt_qty and net_qty.

Please advise
Thanks in advance

[urtrivedi]

SELECT  view_bom_total.mat_code1, 
Sum(view_bom_total.net_qty) net_qty, 
Sum(view_mrir_esd.acpt_qty) FROM view_bom_total 
inner join view_mrir_esd 
ON view_bom_total.mat_code1=view_mrir_esd.mat_code1 
GROUP BY view_bom_total.mat_code1

[adam_k]

Well I'm not sure if this is MySQL (as your first sentence says) or MS SQL as the forum you are posting in, but anyway.

It seems that you are getting a cartesian product, because you are joining 2 tables without proper keys to make each record unique for the other table to match against.
In this case, aggregate the values separately and join them for the final query.

SELECT  mat_code1, net_qty ,acpt_qty  
FROM (select mat_code1, mat_id, sum(net_qty) as 'net_qty' from view_bom_total 
group by mat_code1, mat_id) as a 
inner join
(select mat_id, sum(acpt_qty) as 'acpt_qty' from view_mrir_esd 
group by mat_id ) b 
ON a.mat_id = b.mat_id 

PS: I haven't tried this so it might contain typos
PS 2: You might need to add mat_code1 in the second subquery if it's not unique for each mat_id and use that in the join as well.