combining two queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

combining two queries

Author

Topic

squidol
Starting Member

1 Post

Posted - 2011-03-18 : 05:18:46

i have two queries below which display correct data.

SELECT c.cust_name,soh.so_num, IF(srh.status="4", COUNT(srd.release_id), 0) AS serve_lines
FROM sales_order_header soh
LEFT JOIN sales_release_header srh ON soh.so_id = srh.so_id
LEFT JOIN sales_release_detail srd ON srh.release_id = srd. release_id
LEFT JOIN customer c ON soh.bill_to = c.cust_id
WHERE soh.otype_code='DRCT'
AND soh.comp_id = -'PSORG3935' AND soh.div_id = '137' AND soh.otype_code = 'DRCT'
GROUP BY soh.so_num;

SELECT c.cust_name, soh.so_num, COUNT(sod.so_id) AS order_lines, IF(srh.status="4", COUNT(srd.release_id), 0) AS serve_lines
FROM sales_order_header soh
LEFT JOIN sales_order_detail sod ON soh.so_id = sod.so_id
LEFT JOIN sales_release_header srh ON (soh.so_id = srh.so_id AND srh.otype_code = soh.otype_code AND srh.comp_id = soh.comp_id
AND srh.div_id = soh.div_id AND soh.otype_code = 'DRCT')
LEFT JOIN sales_release_detail srd ON srh.release_id = srd.release_id
LEFT JOIN customer c ON soh.bill_to = c.cust_id
WHERE soh.otype_code='DRCT'
AND soh.comp_id = 'PSORG3935' AND soh.div_id = '137' AND soh.otype_code = 'DRCT'
GROUP BY soh.so_num;

when i try to
combine these two, it display incorrect data because of the LEFT JOIN

SELECT c.cust_name, soh.so_num, COUNT(sod.so_id) AS order_lines, IF(srh.status="4", COUNT(srd.release_id), 0) AS serve_lines
FROM sales_order_header soh
LEFT JOIN sales_order_detail sod ON soh.so_id = sod.so_id
LEFT JOIN sales_release_header srh ON (soh.so_id = srh.so_id AND srh.otype_code = soh.otype_code AND srh.comp_id = soh.comp_id
AND srh.div_id = soh.div_id AND soh.otype_code = 'DRCT')
LEFT JOIN sales_release_detail srd ON srh.release_id = srd.release_id
LEFT JOIN customer c ON soh.bill_to = c.cust_id
WHERE soh.otype_code='DRCT'
AND soh.comp_id = 'PSORG3935' AND soh.div_id = '137' AND soh.otype_code = 'DRCT'
GROUP BY soh.so_num;

i've also tried to use union all but it would be impossible to sum the order_lines and serve_lines because they are stored in one column

SELECT temp_table.* FROM (
SELECT c.cust_name,soh.so_num, COUNT(sod.so_id) AS line_count, 'order_lines' indicator
FROM sales_order_header soh
LEFT JOIN sales_order_detail sod ON soh.so_id = sod.so_id
LEFT JOIN customer c ON soh.bill_to = c.cust_id
WHERE soh.otype_code='DRCT'
AND soh.comp_id = 'PSORG3935' AND soh.div_id = '137' AND soh.otype_code = 'DRCT'
GROUP BY soh.so_num

UNION ALL

SELECT c.cust_name,soh.so_num, IF(srh.status="4", COUNT(srd.release_id), 0) AS line_count , 'serve_lines' indicator
FROM sales_order_header soh
LEFT JOIN sales_release_header srh ON soh.so_id = srh.so_id
LEFT JOIN sales_release_detail srd ON srh.release_id = srd. release_id
LEFT JOIN customer c ON soh.bill_to = c.cust_id
WHERE soh.otype_code='DRCT'
AND soh.comp_id = -'PSORG3935' AND soh.div_id = '137' AND soh.otype_code = 'DRCT'
GROUP BY soh.so_num
)
temp_table;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-18 : 05:20:42

are you using MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources