sql guru out there?

Results 1 to 2 of 2

Thread: sql guru out there?

  1. #1
    Join Date
    Dec 1969

    Default sql guru out there?

    Hello,<BR>I am having problems and wondering if someone can help. My goals is as follows:<BR><BR>On our site, we have a section that contains new orders for customer and i need to be able to click on a link and see a list of all products (that are classified as new orders) in those orders so our inventory management will be able to see all what is new still needs to be ordered.<BR><BR>I have 2 tables, the first table is as follows:<BR><BR>TABLE NAME: orders<BR>TABLE FIELDS: <BR>orderID profileID dateoforder date_shipped status shipping_cost paid backorder<BR><BR>TABLE NAME: order_detail<BR>TABLE FIELD: <BR>orderID productID quantity priceperunit packageType wholesale_price<BR><BR>TABLE NAME: product<BR>TABLE FIELD:<BR>productID, name, brand, packageType<BR><BR>MY goal is for our inventory managemer who is in charge of filling new orders to be able to go to a specific page and view all products that are still new. Orders are new if the orders.status = &#039;processing&#039; so the inventory management will just basically see a list something like this:<BR><BR>EX: (quantity outstanding) order_detail.packageType of product.name by product.brand <BR><BR>(1) 120 tabs of vitamin c by smith company<BR>(8) 140 tabs of vitamin E by johnsoncompany<BR>(6) 120 tabs of vitamin C by joe company<BR>(2) 80 caps of x formula by growth factors<BR><BR>I was thinking something like this:<BR><BR>SQL = "SELECT count(*) FROM orders, order_detail WHERE orders.status = &#039;processing&#039; AND orders.orderID = order_detail.orderID GROUP BY order_detail.productID"<BR><BR>set RS = objConn.Execute(SQL)<BR><BR>&#039;This would pull the records that have a status as processing and get a count <BR><BR>WHILE NOT RS.EOF<BR> SQL = "SELECT * FROM product WHERE productID = RS("productID")"<BR> SET objRS = objConn.Execute(SQL)<BR> Response.Write RS(count) & " " & RS(packtype) &" of " & objRS("name") & " by " objRs("brand")<BR> RS.MoveNext<BR>WEND<BR><BR>Hopefully this makes a little sense to someone whop is reading it. Any help would be much appreciated....

  2. #2
    Join Date
    Dec 1969

    Default RE: sql guru out there?

    Try the following query,<BR><BR>SELECT "(" + order_detail.quantity + ") " + order_detail.packageType + " of " + product.name + " by " + product.brand <BR>FROM orders, order_detail, product<BR>WHERE order.orderID = order_detail.orderID <BR>AND product.productID = order_detail.productID

Posting Permissions

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