Professional Web Applications Themes

Should I use temporary table? - Microsoft SQL / MS SQL Server

You can avoid many temp tables by using a derived table: select c.* , Residents from ( select Country , Residents = count (*) from Customers group by Country ) x join Customers c on c.Country = x.Country The above query shows all customer info plus the number of residents in the corresponding country. -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql "Yaheya Quazi" <edu> wrote in message news:0c1b01c361c4$56b4f550$gbl... Hi I have a table which first I want to run a group by query then with the ...

  1. #1

    Default Re: Should I use temporary table?

    You can avoid many temp tables by using a derived table:

    select
    c.*
    , Residents
    from
    (
    select
    Country
    , Residents = count (*)
    from
    Customers
    group by
    Country
    ) x
    join Customers c on c.Country = x.Country


    The above query shows all customer info plus the number of residents in the corresponding country.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Yaheya Quazi" <edu> wrote in message news:0c1b01c361c4$56b4f550$gbl...
    Hi I have a table which first I want to run a group by
    query then with the result set I want to join it against
    another table.

    My questions are

    1. Should I use a temporary table to temporarily store the
    group by query result?

    2. If I do use a temp table how would I join it with
    another table (some syntax help would be appreciated).

    Thank you.

    Tom Guest

  2. #2

    Default Should I use temporary table?

    No Need of using a Temp Table you can give an alias to the
    group by
    query and then join the other table with this.

    Select a.*
    from TableA a
    join ( select col1 , max(col2) maxcol2 from TableB
    group by col1 ) b on a.col1 = b.col1

    HTH,
    Srinivas Sampangi 
    the 
    sampangi Guest

  3. #3

    Default Re: Should I use temporary table?

    How about giving us the DDL for the other table? In your case, I don't think you want to have order_line_num in your GROUP BY:

    SELECT
    *
    FROM
    (
    SELECT order_num, SUM(item_received) AS items
    FROM master_receiving
    GROUP BY order_num
    ) as x
    JOIN MyTable m on m.order_num = x.order_num



    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Yaheya Quazi" <edu> wrote in message news:uSW4#phx.gbl...
    Thanks! I am not such a good SQL programmer to understand your SQL
    completely so bare with me please...here is the actual sql that I want
    to run....I Want to join the order_number with another table with this
    group by sql..

    SELECT order_num, order_line_num, SUM(item_recieved) AS items
    FROM master_receiving
    GROUP BY order_num, order_line_num



    University of California at Merced


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Tom Guest

  4. #4

    Default Re: Should I use temporary table?

    other table is order_detail_org I want to get the item_description from
    order_detail_org with an LEFT OUTER JOIN with order_detail_org on
    order_num and order_line_num

    DDL meaning what are the field types and such ? If so,

    order_num is char(12)
    order_line_numi is smallint
    item_recieved is smallint

    Thanks for helping out...

    University of California at Merced


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Yaheya Guest

  5. #5

    Default Re: Should I use temporary table?

    So maybe you want:

    select
    *
    from
    order_detail_org as o
    left join
    (
    SELECT order_num, order_line_num, SUM(item_recieved) AS items
    FROM master_receiving
    GROUP BY order_num, order_line_num
    ) as x on x.order_num = o.order_num
    and x.order_line_num = o.order_line_num


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Yaheya Quazi" <edu> wrote in message news:phx.gbl...
    other table is order_detail_org I want to get the item_description from
    order_detail_org with an LEFT OUTER JOIN with order_detail_org on
    order_num and order_line_num

    DDL meaning what are the field types and such ? If so,

    order_num is char(12)
    order_line_numi is smallint
    item_recieved is smallint

    Thanks for helping out...

    University of California at Merced


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!


    Tom Guest

  6. #6

    Default Re: Should I use temporary table?

    ok folks I think I got it working...but can you check to see if you see
    any potential problem...

    SELECT m.order_num, m.order_line_num, m.order_item_desc, x.items
    FROM
    (
    SELECT order_num, SUM(item_recieved) AS items, order_line_num
    FROM master_receiving
    GROUP BY order_num, order_line_num
    ) as x
    JOIN order_detail_org m on m.order_num = x.order_num and
    m.order_line_num = x.order_line_num

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Yaheya Guest

  7. #7

    Default Re: Should I use temporary table?

    You had indicated earlier that you wanted to do a left join. As long as the query you have shown gives you the rows you want, then you should be OK.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Yaheya Quazi" <edu> wrote in message news:#phx.gbl...
    ok folks I think I got it working...but can you check to see if you see
    any potential problem...

    SELECT m.order_num, m.order_line_num, m.order_item_desc, x.items
    FROM
    (
    SELECT order_num, SUM(item_recieved) AS items, order_line_num
    FROM master_receiving
    GROUP BY order_num, order_line_num
    ) as x
    JOIN order_detail_org m on m.order_num = x.order_num and
    m.order_line_num = x.order_line_num

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Tom Guest

  8. #8

    Default Re: Should I use temporary table?

    ok thanks to you...the following works just fine...now one
    problem...master_recieving has a date_stamp field...I want to do the
    same query where master_receiving.date_stamp = Getdate()...how do I do
    that!

    SELECT m.order_num, m.order_line_num, m.order_item_desc,
    m.order_line_qty, m.order_aprv_logon, x.items
    FROM
    (
    SELECT order_num, SUM(item_recieved) AS items, order_line_num
    FROM master_receiving
    GROUP BY order_num, order_line_num
    ) as x
    inner JOIN order_detail_org m on m.order_num = x.order_num and
    m.order_line_num = x.order_line_num



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Yaheya Guest

  9. #9

    Default Re: Should I use temporary table?

    You can use:

    SELECT m.order_num, m.order_line_num, m.order_item_desc,
    m.order_line_qty, m.order_aprv_logon, x.items
    FROM
    (
    SELECT order_num, SUM(item_recieved) AS items, order_line_num
    FROM master_receiving
    WHERE date_stamp = convert (char (8), getdate (), 112)
    GROUP BY order_num, order_line_num
    ) as x
    inner JOIN order_detail_org m on m.order_num = x.order_num and
    m.order_line_num = x.order_line_num


    Note that I used CONVERT(), since I think you want everything for today, not just this moment in time. I am also assuming that date_stamp does not store the time component.


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Yaheya Quazi" <edu> wrote in message news:phx.gbl...
    ok thanks to you...the following works just fine...now one
    problem...master_recieving has a date_stamp field...I want to do the
    same query where master_receiving.date_stamp = Getdate()...how do I do
    that!

    SELECT m.order_num, m.order_line_num, m.order_item_desc,
    m.order_line_qty, m.order_aprv_logon, x.items
    FROM
    (
    SELECT order_num, SUM(item_recieved) AS items, order_line_num
    FROM master_receiving
    GROUP BY order_num, order_line_num
    ) as x
    inner JOIN order_detail_org m on m.order_num = x.order_num and
    m.order_line_num = x.order_line_num



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Tom Guest

Similar Threads

  1. Temporary table
    By Sauron in forum Informix
    Replies: 11
    Last Post: January 21st, 05:32 PM
  2. How to avoid a temporary table
    By Peter in forum Oracle Server
    Replies: 6
    Last Post: October 25th, 10:42 PM
  3. Global Temporary Table
    By Raj in forum Oracle Server
    Replies: 3
    Last Post: October 24th, 01:01 AM
  4. Global Temporary Table
    By Anand in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 17th, 07:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139