44

I have two queries which return separate result sets, and the queries are returning the correct output.

How can I combine these two queries into one so that I can get one single result set with each result in a separate column?

Query 1:

SELECT SUM(Fdays) AS fDaysSum From tblFieldDays WHERE tblFieldDays.NameCode=35 AND tblFieldDays.WeekEnding=?

Query 2:

SELECT SUM(CHdays) AS hrsSum From tblChargeHours WHERE tblChargeHours.NameCode=35 AND tblChargeHours.WeekEnding=?

Thanks.

jpmc26
  • 28,463
  • 14
  • 94
  • 146
W A K A L E Y
  • 817
  • 1
  • 10
  • 14
  • `SELECT SUM("+Fdays+")AS fDaysSum, SUM("+CHdays+") AS hrsSum`? – David Starkey May 03 '13 at 17:06
  • You want a [join or a union](http://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union)? Perhaps you can show some example data and desired output. – Tim Lehner May 03 '13 at 17:12
  • @DavidStarkey I tried it in the same way but i dont know how to give different where conditions for different DB – W A K A L E Y May 03 '13 at 17:17
  • @TimLehner I need union. I tried Query_1 UNION ALL Query_2 it works but the Result set give 2 rows with 1 column instead of which I want 2 columns and 1 rows i.e. both values in sible rs. next() call. – W A K A L E Y May 03 '13 at 17:20
  • 2
    @user1614217 Then you don't need a `UNION`, you need a `CROSS JOIN`. – Lamak May 03 '13 at 17:23

4 Answers4

91

You can aliasing both query and Selecting them in the select query
http://sqlfiddle.com/#!2/ca27b/1

SELECT x.a, y.b FROM (SELECT * from a) as x, (SELECT * FROM b) as y
Willy Pt
  • 1,795
  • 12
  • 20
  • I have one more issue. consider that i am getting values from first query as X and i need to use one of that value in Query 2 how it can be done? – W A K A L E Y May 03 '13 at 18:06
  • Is the query2 on a different line of php? you can put the `(SELECT...) as x` to your new query and use it as parameter from the alias (x.a).. Or if it's on different line on php, consider storing the result of your needed value and use it as a parameter when exexuting your sql query on PHP – Willy Pt May 03 '13 at 18:13
  • I thought the same and tried it gives me Error: Too Few Parameter – W A K A L E Y May 03 '13 at 18:20
  • can you update your question with your current query? and the error? – Willy Pt May 03 '13 at 18:25
  • Its on the same line. I tried to use it as alias it gives me Error: Too Few Parameter – W A K A L E Y May 03 '13 at 18:26
  • here is my query : `SELECT * FROM (SELECT NameCode FROM tlbNames) as X, (Select SUM(Mo+Tu) FROM tblFieldDays WHERE tblFieldDays.NameCode=X.NameCode) as Y` . It gives me Too few parameters Error – W A K A L E Y May 03 '13 at 18:33
  • You can't use it like that. Consider using IN. `SELECT SUM(Mo+Tu) FROM tblFieldDays WHERE NameCode IN ( ...YOUR X alias code) ` – Willy Pt May 03 '13 at 18:38
  • I am not able to figure it out. Will you post the complete query? Sorry as I am new to sql I am stuck bit here. Thanks – W A K A L E Y May 03 '13 at 18:50
  • `SELECT NameCode, SUM(Mo+Tu) FROM tblFieldDays WHERE NameCode IN (SELECT NameCode FROM tblNames)` ... I'm using mobilephone right now and myquery might be wrong. please do report back whether it works or not – Willy Pt May 03 '13 at 18:58
  • No it didnt. What i think is `IN (SELECT .. ) `will make it give same value every time. So my basic question is : ` SELECT * FROM (SELECT RNameCode FROM tblName) AS X, (SELECT * FROM tblFieldDays WHERE NameCode =(NOW HERE I WANT TO GET THE VALUE OF THAT RNameCode)) AS Y` – W A K A L E Y May 03 '13 at 19:17
16

You can use a CROSS JOIN:

SELECT *
FROM (  SELECT SUM(Fdays) AS fDaysSum 
        FROM tblFieldDays 
        WHERE tblFieldDays.NameCode=35 
        AND tblFieldDays.WeekEnding=1) A -- use you real query here
CROSS JOIN (SELECT SUM(CHdays) AS hrsSum 
            FROM tblChargeHours 
            WHERE tblChargeHours.NameCode=35 
            AND tblChargeHours.WeekEnding=1) B -- use you real query here
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • At first I was wondering if this was a joke, but it is a solution if the queries will always only return one row (obviously will with only aggregates) and the OP doesn't mind putting those params in twice. – Tim Lehner May 03 '13 at 17:23
  • 1
    @TimLehner No joke here. And even if I saw that the derived tables return one row, I should've clarified that this works fine because of it – Lamak May 03 '13 at 17:25
  • @TimLehner It give me Error which says Synatx Error in FROM clause Here is my code: `String t1 = "SELECT * "+ "FROM ( SELECT SUM("+Fdays+")AS fDaysSum "+ "FROM tblFieldDays "+ "WHERE tblFieldDays.NameCode=35 AND tblFieldDays.WeekEnding=?) "+ "CROSS JOIN (SELECT SUM("+CHdays+") AS hrsSum "+ " FROM tblChargeHours WHERE tblChargeHours.NameCode=35 AND tblChargeHours.WeekEnding=?)";` – W A K A L E Y May 03 '13 at 17:34
  • @user1614217 Maybe it was the alias for the first query, I didn't have an space there. Try now my updated answer – Lamak May 03 '13 at 17:35
  • @Lamak Same issue, Infact i did the spacing thing earlier also – W A K A L E Y May 03 '13 at 17:44
9

You could also use a CTE to grab groups of information you want and join them together, if you wanted them in the same row. Example, depending on which SQL syntax you use, here:

WITH group1 AS (
  SELECT testA
    FROM tableA
),
group2 AS (
  SELECT testB
    FROM tableB 
)
SELECT *
  FROM group1
  JOIN group2 ON group1.testA = group2.testB --your choice of join
;

You decide what kind of JOIN you want based on the data you are pulling, and make sure to have the same fields in the groups you are getting information from in order to put it all into a single row. If you have multiple columns, make sure to name them all properly so you know which is which. Also, for performance sake, CTE's are the way to go, instead of inline SELECT's and such. Hope this helps.

AS4noob
  • 123
  • 1
  • 5
-2

how to club the 4 query's as a single query

show below query

  1. total number of cases pending + 2.cases filed during this month ( base on sysdate) + total number of cases (1+2) + no. cases disposed where nse= disposed + no. of cases pending (other than nse <> disposed)

nsc = nature of case

report is taken on 06th of every month

( monthly report will be counted from 05th previous month to 05th present of present month)

ramreddy aredla
  • 1