Post reply

Table join!

  • December 29, 2009 at 5:44 am

    #217919

    Hi to all,

    I'll try to explain my Issue as briefly & clearly as possible for me...

    What I need to do:

    I need to join two tables(say 'jan' and 'feb'),each having two columns 'query' and 'querycount' respectievely.The column 'query' contains text data(names,placenames etc..) and the 'querycount' column contains numeric values(all +ve numbers).The 'query' columns in both the tables 'jan' & 'feb' have many values in common with each other.So,what I need to do is to join both the 'jan' & 'feb' tables together such that only unique values remain in the 'query' column and for each value in the 'query' column that remains,the corresponding values in the 'querycount' column should be added (i.e the corresponding values from both the 'jan' & 'feb' tables).

    What I know so far:

    let me explain my problem a little further with the aid of a pictorial example...

    Let's Say I have two tables 'football' n 'baseball' as follows:

    now I know that bothe the tables can be joined by a 'UNION' statement as follows:

    SELECT name,age FROM football

    UNION

    SELECT name,age FROM baseball

    .....so that the output is as follows:

    So that part's clear to me. However my tables are as follows:

    'jan' table:

    and

    'feb' table:

    so I want the output as follows:

    output:

    ....As you can see I need the unique terms (from the 'query' columns of each table )along with their corresponding values (from 'querycount' column) added from both the tables 'jan' and 'feb'.

    I'm kind of stuck here without being able to do the same... 🙁

    Thank you.

    can someone help me..?

    Any help is appreciated.Thanks in advance.

  • December 29, 2009 at 5:54 am

    #1096985

    something like this ?

    with cteUnion()

    as

    (

    Select query,querycount from jan

    union all

    Select query,querycount from feb

    union all

    Select query,querycount from mar

    )

    Select query,sum(querycount)

    from cteUnion

    group by query


    Clear Sky SQL
    My Blog[/url]

  • December 29, 2009 at 6:00 am

    #1096989

    select a.query,sum(a.querycnt+b.querycnt) from #jan a ,#feb b

    where a.query=b.query

    group by a.query

  • December 29, 2009 at 6:18 am

    #1096997

    >Dave

    you just saved a person frm hanging himself man..;)

    ......jokes apart, thanks a million man.That's Exactly what I needed.Thanks:-)

  • December 29, 2009 at 6:21 am

    #1096999

    >vyas

    Thank you very much mate:-)

  • December 29, 2009 at 6:31 am

    #1097003

    vyas (12/29/2009)

    select a.query,sum(a.querycnt+b.querycnt) from #jan a ,#feb b

    where a.query=b.query

    group by a.query

    Will need to be a full outer join.....


    Clear Sky SQL
    My Blog[/url]

  • December 30, 2009 at 3:58 am

    #1097374

    Hi,

    I did as Dave suggested and got the results:) and there are more than 30 million rows in the resultant table.However in the mad dash to execute the query and see the results,I forgot to specify to create a new table and put the results in it:(

    Is there any way by which I can put the Query Results into a new table after executing it,So I can run some queries against the data (in the new table)????

    BTW I already saved as the results as an external text file.

    Thank you.

  • December 30, 2009 at 4:33 am

    #1097381

    coldfire101 (12/30/2009)

    Is there any way by which I can put the Query Results into a new table after executing it,So I can run some queries against the data (in the new table)????

    BTW I already saved as the results as an external text file.

    Thank you.

    with cteUnion()

    as

    (

    Select query,querycount from jan

    union all

    Select query,querycount from feb

    union all

    Select query,querycount from mar

    )

    Select query,sum(querycount)

    INTO Result

    from cteUnion

    group by query

    the output will be saved in Result table

    -Vikas Bindra

  • December 30, 2009 at 4:35 am

    #1097382

    coldfire101 (12/30/2009)

    Hi,

    BTW I already saved as the results as an external text file.

    Thank you.

    You can use BCP.exe utility to import the result from the file into a table.

    -Vikas Bindra

  • December 30, 2009 at 5:10 am

    #1097389

    >Vikas

    Thank you Mate:)

Viewing 10 posts - 1 through 9 (of 9 total)

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