November 17, 2004 at 9:18 am
Hi,
I have to combine two quries to show data in my report how can i do it?
My both queries are different,
FIRST QUERY IS
SELECT COUNT(tbl_IB_Issue.CustomerIssueID) AS PQRcount, tbl_PM_project.ProjectName as ProjectId
FROM tbl_IB_Issue JOIN
tbl_PM_Project ON tbl_PM_Project.ProjectID = tbl_IB_Issue.ProjectID
GROUP BY tbl_PM_project.ProjectName
SECOND QUERY is
SELECT tbl_PM_project.ProjectName AS projectname, tbl_PM_Employee.EmployeeName as responsible, COUNT(tbl_IB_Issue.CustomerIssueID) AS pqrcount
FROM tbl_IB_Issue INNER JOIN
tbl_PM_Project ON tbl_IB_Issue.ProjectID = tbl_PM_Project.ProjectID JOIN
tbl_PM_Employee ON tbl_PM_Employee.EmployeeID= tbl_IB_ISsue.AssignTo
GROUP BY tbl_PM_Employee.EmployeeName, tbl_PM_Project.ProjectName
ORDER BY tbl_PM_Project.ProjectName,tbl_PM_Employee.EmployeeName
COMPUTE SUM(count(tbl_IB_Issue.customerissueid)) by tbl_PM_Project.ProjectName
November 17, 2004 at 9:35 am
Hi,
You use the UNION between the two statements.
November 17, 2004 at 9:40 am
Hi,
If these two statements retrives different values then go go for temp tables.Keep all the results in the temp tables and then retrieve it from that table.
November 17, 2004 at 9:40 am
Hi Eswar,
Thanx for your reply, but it doesn't work in that way giving error by saying
err msg The column prefix 'tbl_PM_Project' does not match with a table name or alias name used in the query.
November 17, 2004 at 9:42 am
Can you please reply in detail how to do it, because i am new to Sql server
Thanx
November 17, 2004 at 10:04 am
Are you busy
November 17, 2004 at 10:13 am
Assuming you want something like this in the reult set:
1stquery.COL1, 1stquery.COL2, 2ndquery.COL3......
Then try:
select A.COL1, A.COL2, B.COL1, B.COL2
from (1st query) A, (2nd query) B
where A.ID=B.ID
I know this appears to over-simplify the problem but the fundementals are the same.
November 18, 2004 at 9:37 am
hey veena
what is the purpose of joining the two queries?iam not clear. y not execute the two sql statements as batch statements. i mean execute both the select statements together......
the union operator works only when two select statements return the same number of columns and same data type.
or else as said by Osoba u need to join two select statements.
if u can still make it clear i might try to help u out.
Rajiv.
November 18, 2004 at 9:49 am
Hi Veena,
You can try this one...it should work, you might have to use some other Aggregeate function in outer query if Sum alters the output....
select sum(t.PQRCount), t.ProjectName, t.EmployeeName
FROM (
SELECT COUNT(tbl_IB_Issue.CustomerIssueID) AS PQRcount, tbl_PM_project.ProjectName as projectname,
'Dummy' as EmployeeName
FROM tbl_IB_Issue JOIN
tbl_PM_Project ON tbl_PM_Project.ProjectID = tbl_IB_Issue.ProjectID
GROUP BY tbl_PM_project.ProjectName
union all
SELECT COUNT(tbl_IB_Issue.CustomerIssueID) AS pqrcount,
tbl_PM_project.ProjectName AS projectname, tbl_PM_Employee.EmployeeName as responsible,
COUNT(tbl_IB_Issue.CustomerIssueID) AS pqrcount
FROM tbl_IB_Issue INNER JOIN
tbl_PM_Project ON tbl_IB_Issue.ProjectID = tbl_PM_Project.ProjectID JOIN
tbl_PM_Employee ON tbl_PM_Employee.EmployeeID= tbl_IB_ISsue.AssignTo
GROUP BY tbl_PM_Employee.EmployeeName, tbl_PM_Project.ProjectName
) t
group by t.ProjectName, t.EmployeeName
ORDER BY t.ProjectName, t.EmployeeName
COMPUTE SUM(sum(PQRCount)) by t.ProjectName
November 18, 2004 at 12:06 pm
Shot in a dark, but you can add 0 ( zeroes) to the first union with the smaller amount of rows:
Select Customerid, 0 from Orders
Union
Select CustomerID, MemberID from your table
November 18, 2004 at 4:21 pm
I think this is a reporting issue, not a SQL issue.
Look for help with whatever tool you're using for reporting. What will be executing the two queries?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply