Professional Web Applications Themes

need help with query - Microsoft SQL / MS SQL Server

I have two tables (in parent-child relationship) Orders: Id, Name, Type Items: Id, OrderId, Name, Type, Price I want to do a select so that result looks like Result: Orders.Id, Orders.Name, Orders.Type, Count of Items for this Orders.Id But I cannot figure out how to do this (count) in the same query. Can someone please help? Thanks db...

  1. #1

    Default need help with query

    I have two tables (in parent-child relationship)

    Orders:
    Id, Name, Type

    Items:
    Id, OrderId, Name, Type, Price

    I want to do a select so that result looks like

    Result:

    Orders.Id, Orders.Name, Orders.Type, Count of Items for this Orders.Id

    But I cannot figure out how to do this (count) in the same query. Can
    someone please help?

    Thanks

    db
    Dan Guest

  2. #2

    Default Re: need help with query

    You can try:
    SELECT o.ID, o.Name, o.Type, CountOrders = (SELECT COUNT(i.*) FROM Items i
    WHERE i.OrderID = o.ID)
    FROM Orders o
    --
    HTH,
    SriSamp
    Please reply to the whole group only!

    "Dan" <danjbart> wrote in message
    news:fa556b4d.0303260813.6046a83cposting.google.c om...
    > I have two tables (in parent-child relationship)
    >
    > Orders:
    > Id, Name, Type
    >
    > Items:
    > Id, OrderId, Name, Type, Price
    >
    > I want to do a select so that result looks like
    >
    > Result:
    >
    > Orders.Id, Orders.Name, Orders.Type, Count of Items for this Orders.Id
    >
    > But I cannot figure out how to do this (count) in the same query. Can
    > someone please help?
    >
    > Thanks
    >
    > db

    SriSamp Guest

Similar Threads

  1. Query of Query to select a title first letter
    By Conti in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 18th, 11:42 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 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