Counting Details on a Master-Detail Relatioship

Counting Details on a Master-Detail Relatioship

Post by Maurizi » Thu, 27 May 1999 04:00:00



Hi all,

Maybe I'm doing something wrong with my query, but I'm facing the following
problem: I need to retrieve all records from a Master table (say Customers)
and the number of Details (say Orders) having the result in just one
recordset. For example:

Customer 1, Address 1, [...], Number of orders 1
Customer 2, Address 2, [...], Number of orders 2
[...]

Please help.
Thanks.

 
 
 

Counting Details on a Master-Detail Relatioship

Post by Alexander Tarasu » Thu, 27 May 1999 04:00:00


select CustomerName,CustomerAddress,
(select count(*) from orders where orders.customerid=customers.customerid)
as ordercount
from customers

-------------------------------------------
Alexander Tarasul
Microsoft Certified Solution Developer(SQL Server,VB)
Certified Borland Delphi Client Server Developer

http://pages.ripco.com/~shleym
SQL Answers located at
http://pages.ripco.com/~shleym/sql_answers.htm

---------------


>Hi all,

>Maybe I'm doing something wrong with my query, but I'm facing the following
>problem: I need to retrieve all records from a Master table (say Customers)
>and the number of Details (say Orders) having the result in just one
>recordset. For example:

>Customer 1, Address 1, [...], Number of orders 1
>Customer 2, Address 2, [...], Number of orders 2
>[...]

>Please help.
>Thanks.


 
 
 

Counting Details on a Master-Detail Relatioship

Post by Tony Rogerso » Thu, 27 May 1999 04:00:00


SELECT Customer, Address, [...], d.NumberOfOrders
FROM ( SELECT customer_id,
                                Numberoforders= COUNT(*)
               FROM Orders
               GROUP BY customer_id
             ) as d
                INNER JOIN Customer c ON ( c.customer_id = d.customer_id )

--
Tony Rogerson MCP,SQL Server MVP
Torver Computer Consultants Ltd.


Quote:> Hi all,

> Maybe I'm doing something wrong with my query, but I'm facing the
following
> problem: I need to retrieve all records from a Master table (say
Customers)
> and the number of Details (say Orders) having the result in just one
> recordset. For example:

> Customer 1, Address 1, [...], Number of orders 1
> Customer 2, Address 2, [...], Number of orders 2
> [...]

> Please help.
> Thanks.

 
 
 

Counting Details on a Master-Detail Relatioship

Post by Maurizi » Sat, 29 May 1999 04:00:00


Thank you very much, guys !
 
 
 

1. Master Detail Relatioships in Delphi 4

I have recently installed Delphi 4.

Without the patch, master detail relatioships work fine.

With the patch (d4cupd1.exe), the master detail relationships stop
working :(

Can anyone help?


Orbital Decisions  Providing Simple Answers to Complex Problems
Orbital URL: http://www.orbital.co.za/contents.htm
Components/Apps: http://www.orbital.co.za/orbcomp.htm
User Group URL: http://www.orbital.co.za/delphi.htm
David URL: http://www.orbital.co.za/cv.htm

2. Reading a Recordset while asynchronously Fetching it.

3. Selecting from Master/Detail w/ Detail as columns of Master

4. Oracle Developer-Houston

5. master - detail in one row, details in one extra column

6. Is there a slick and efficient way to do this query?

7. Master-detail-detail-detial with Cascade delete

8. hiding system menu & speed bar & more

9. Sorting detail records in a master detail relation.

10. Problem when inserting records in the detail table (master/detail relation)

11. Master->Detail->Detail Query Too Slow

12. Master-Detail : Sorting Detail?

13. Link multiple datagrids to a single record (Master\Detail Form with multiple details)