How to retrieve data from two sql queries??

  • 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

     

     

     

     

  • Hi,

    You use the UNION between the two statements.

     

  • 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.

  • 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.

  • Can you please reply in detail how to do it, because i am new to Sql server

    Thanx

  • Are you busy

  • 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.

  • 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.

  • 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

  • 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

  • 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