Select from (Multiple Tables Sub Query)

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select from (Multiple Tables Sub Query)

Author

Topic

stephena
Starting Member

2 Posts

Posted - 2009-07-07 : 04:52:39

[code]
SELECT USERS.nt_username, SUM(SUBQU.CALL) AS IncompleteLines, SUM(SUBQU.CALL) AS IncompleteLines
FROM tblFixedLine_provisioners AS USERS LEFT OUTER JOIN
(SELECT tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_call_1.fllog_ref) AS CALL
FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
tblFixedLine_call AS tblFixedLine_call_1 ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_1.fllog_ref
WHERE (tblFixedLine_call_1.status <> 'Order Complete') AND (tblFixedLine_call_1.status <> 'Cancelled')
GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
UNION ALL
SELECT tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_orderdetails_broadband.fllog_ref) AS BB
FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
tblFixedLine_orderdetails_broadband AS tblFixedLine_orderdetails_broadband ON
tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_orderdetails_broadband.fllog_ref
WHERE (tblFixedLine_orderdetails_broadband.status <> 'Order Complete') AND (tblFixedLine_orderdetails_broadband.status <> 'Cancelled')
GROUP BY tblFixedLine_order_summary_1.current_fl_advisor) AS SUBQU ON USERS.nt_username = SUBQU.current_fl_advisor LEFT OUTER JOIN
(SELECT tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_call_1.fllog_ref) AS CALL
FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
tblFixedLine_call AS tblFixedLine_call_1 ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_1.fllog_ref
WHERE (tblFixedLine_call_1.status <> 'Order Complete') AND (tblFixedLine_call_1.status <> 'Cancelled') AND (DATEPART(wk,
tblFixedLine_call_1.created_date) = DATEPART(wk, GETDATE()))
GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
UNION ALL
SELECT tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_orderdetails_broadband.fllog_ref) AS BB
FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
tblFixedLine_orderdetails_broadband AS tblFixedLine_orderdetails_broadband ON
tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_orderdetails_broadband.fllog_ref
WHERE (tblFixedLine_orderdetails_broadband.status <> 'Order Complete') AND (tblFixedLine_orderdetails_broadband.status <> 'Cancelled') AND
(DATEPART(wk, tblFixedLine_orderdetails_broadband.created_date) = DATEPART(wk, GETDATE()))
GROUP BY tblFixedLine_order_summary_1.current_fl_advisor) AS SUBQU1 ON USERS.nt_username = SUBQU1.current_fl_advisor
GROUP BY USERS.nt_username, SUBQU.current_fl_advisor
[/code]

The results of the above code, SUBQU should look like:

Username | 3

SUBQU1 should look like:

Username | 3

And if i run those tables individually they are correct but when i join the two to create one output (as the code above should do) its doubles these up like:

Username | 6 | 6

Im beaten i have no idea why? Can you help?

P.S. I hope i have posted this in the correct forum, im using Visual Studio 2005 and have basic knowldege of SQL but i need to get some pretty large reports out of my database and its blowing my tiny little mind!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-07 : 05:16:58

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources