How to merge two sql select statement results I tried it but it not get please see it in detailed explination with my query

  • Hi All,

    My Query Goes Like This,

    Select TT.ID,TT.NAME,ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS

    From MYTABLE1 as TT INNER JOIN MYTABLE2 as TB

    On TT.ID=TB.ID INNER JOIN MYTABLE3 as TH

    On TB.BID=TH.BID INNER JOIN MYTABLE4 as TJA

    On TJA.HID=TH.HID

    Where TJA.JID=41

    group by TT.ID,TT.NAME

    UNION

    Select TJA.ID,TT.NAME,ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS

    From MYTABLE1 as TT INNER JOIN MYTABLE2 as TJA

    On TT.ID=TJA.ID

    wHERE TJA.JID=41

    group by TJA.ID,TT.NAME

    The First SQL Statement Result was like below one,

    IDNAMEHOURS

    1AAA0

    2BBB10

    3CCC0

    4DDD0

    The Second SQL Statement Result was like below one,

    IDNAMEHOURS

    1AAA20

    2BBB0

    3CCC0

    4DDD0

    After writing the The above UNION Statement i get like below one

    IDNAMEHOURS

    1AAA0

    1AAA20

    2BBB0

    2BBB10

    3CCC0

    4DDD0

    It was wrong

    I want to get result like below one

    IDNAMEHOURS

    1AAA20

    2BBB10

    3CCC0

    4DDD0

    Please give solution to me

    Thanks In Advance,

    VenkiDesai.

  • What would be the expected output if your results looked like this and why?

    The Second SQL Statement Result was like below one,

    ID NAME HOURS

    1 AAA 20

    2 BBB 30

    3 CCC 0

    4 DDD 0

    After writing the The above UNION Statement i get like below one

    ID NAME HOURS

    1 AAA 10

    2 BBB 10

    3 CCC 0

    4 DDD 0


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • I'm short on time right now, but if you adapt this to your tables, it should get you what you need

    select Table_1.ID, Table_1.name, (sum(Table_1.hours) + sum(Table_2.hours)) as hours

    from Table_1 inner join Table_2

    on Table_1. id = Table_2.id

    and Table_1.name = Table_2.name

    group by Table_1.ID, Table_1.name

    would give

    1 AAA 20

    2 BBB 10

    3 CCC 0

    4 DDD 0

    Bex

  • Some more sample data from your tables would be extremely helpful, not just the results.

    --------
    For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
    You can also follow my twitter account to get daily updates: @BLantz2455

  • This is because the UNION keyword combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

    Due to the brevity of the sample data and results, I assume that you are wanting the total hours across both queries for each ID. If that is the case you will need to write an aggregate with GROUP BY, something like this:

    select t.ID, t.NAME, sum(t.HOURS)

    from (

    select ID, NAME, HOURS

    from first_query_results

    union all

    select ID, NAME, HOURS

    from second_query_results

    ) t

    group by t.ID, t.NAME

  • UNION on it own dedupes the result set which is almost certainly not what you want.

    Try combining the two queries into one so you're not reading tables twice, unnecessarily:

    SELECT

    TT.ID, TT.NAME,

    ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS,

    MAX(x.[HOURS]) AS Hours2

    FROM MYTABLE1 as TT

    INNER JOIN MYTABLE2 as TB

    On TT.ID=TB.ID

    INNER JOIN MYTABLE3 as TH

    On TB.BID=TH.BID

    INNER JOIN MYTABLE4 as TJA

    On TJA.HID=TH.HID

    CROSS APPLY (

    SELECT ROUND((CAST((SUM(TJA.MINS)) AS FLOAT)/60),0) AS [HOURS]

    FROM MYTABLE2_FromSecondQuery q2

    WHERE q2.ID = TT.ID

    ) x

    WHERE TJA.JID=41

    GROUP BY TT.ID,TT.NAME

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply