Professional Web Applications Themes

Joining a table to a procedure - Microsoft SQL / MS SQL Server

From a Newbie (fresh off the boat from Access land). Please Help We have a simple bunch of Access queries (procedures/views) similar to the following but cannot get them to work in SQL Server. We are missing something obvious and would be very grateful to know what. We have a database with three simple, obvious and normalized tables called Products, Orders and Order_Lines. We wish to produce a procedure with an output like: Prod. Desc. Order Qty A AAAA 1000 2 B BBBB 1000 5 C CCCC D DDDD 1000 1 E EEEE 1000 4 F FFFF which is a ...

  1. #1

    Default Joining a table to a procedure

    From a Newbie (fresh off the boat from Access land).
    Please Help

    We have a simple bunch of Access queries
    (procedures/views) similar to the following but cannot get
    them to work in SQL Server. We are missing something
    obvious and would be very grateful to know what.

    We have a database with three simple, obvious and
    normalized tables called Products, Orders and Order_Lines.

    We wish to produce a procedure with an output like:

    Prod. Desc. Order Qty
    A AAAA 1000 2
    B BBBB 1000 5
    C CCCC
    D DDDD 1000 1
    E EEEE 1000 4
    F FFFF

    which is a list ALL products with the quantity (if any) of
    each product ordered under a given order number (in this
    case, Order Number 1000). In this example, Order 1000 was
    only for Products A,B,D and E. (Indeed, we do not need the
    column with the order number. I only show it here for
    clarity.)

    Since the Order_Lines table contains many records for many
    different orders and many different products, it is
    essential to FIRST eliminate all the inappropriate rows
    with a filter such as:

    Select Product_Code, Quantity from Order_Lines where
    Order_Number = [parameter] (e.g. "1000")

    In Access, we would create this simple query and call it
    something like "qryOrder_Lines_Filtered".

    Now all we have to do is left join it to the Products
    table like:

    Select Product_Code, Product_Description, Quantity from
    Products Left Outer Join qryOrderLines_Filtered where
    Products.Order_Number =
    qryOrder_Lines_Filtered.Order_Number

    All of this model/logic etc is standard (in Access) but,
    for some reason, the procedure is not happy to allow a
    join between a table and another procedure.

    What are we doing wrong.

    Thank you.

    John Guest

  2. #2

    Default Re: Joining a table to a procedure

    You forgot to tell us which version of SQL Server you're using!

    If you can't use a table-valued function as Lenny suggests (e.g. you're
    using 6.5 or 7.0, or you don't have the ability to write/add a function),
    you can create a #temp table, insert the stored procedure into the #temp
    table, and join against that.



    "John" <co.uk> wrote in message
    news:93b101c35b9d$b1193c40$gbl... 


    Aaron Guest

  3. #3

    Default Re: Joining a table to a procedure


    Thanks guys

    We'll go and follow Lenny's stuff.

    Since we are using SQL Server 2000 I expect that this will
    allow us to do it.

    Thanks again

    John
     
    using! 
    suggests (e.g. you're 
    write/add a function), 
    into the #temp [/ref]
    get [/ref]
    Order_Lines. [/ref]
    of [/ref]
    was [/ref]
    the [/ref]
    many 
    >
    >
    >.
    >[/ref]
    John Guest

  4. #4

    Default Re: Joining a table to a procedure

    Have I missed something here?

    What's wrong with:

    Select Product_Code, Product_Description, Quantity from Products Left Outer
    Join (Select Product_Code, Quantity from Order_Lines where Order_Number =
    [parameter]) qryOrderLines_Filtered where Products.Order_Number =
    qryOrder_Lines_Filtered.Order_Number

    Cheers,
    Dave

    "John" <co.uk> wrote in message
    news:93b101c35b9d$b1193c40$gbl... 


    Dave Guest

Similar Threads

  1. Joining 2 columns from the same table
    By Nicos in forum MySQL
    Replies: 7
    Last Post: September 22nd, 08:17 AM
  2. Large table join using "text" as the joining key
    By howachen@gmail.com in forum MySQL
    Replies: 4
    Last Post: September 5th, 03:08 PM
  3. Stored Procedure and DB Table
    By the-african in forum Coldfusion Database Access
    Replies: 0
    Last Post: November 23rd, 04:56 PM
  4. queryNew() joining to physical table
    By DannoParker in forum Coldfusion Database Access
    Replies: 2
    Last Post: September 7th, 10:41 PM
  5. Collation problem joining a table and view........
    By Paul in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 31st, 02:14 PM

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