dlarkin77
July 3rd, 2008, 04:30 PM
Hi,
I have 3 tables : Products, Orders and OrderDetails.
The Products table has a ProductCode field
The Orders table has an AccountNumber, DeliveryDate field and an ID field
The OrderDetails has a foreign key that points to the ID field in Orders
The OrderDetails has a foreign key that points to the ProductCode in Products
I need to get a list of all ProductCodes
For each ProductCode I need to get the most recent Orders.DeliveryDate and OrderDetails.Quantity where Order.AccountNumber equals a given AccountNumber
For example assume each table contains the following records:
Products Table
ProductCode
A1
B2
C3
D4
E5
OrderDetails Table
ID ProductCode Quantity
1 A1 1
1 B2 2
1 D4 4
2 C3 1
2 E5 2
Orders Table
ID AccountNumber DeliveryDate
1 1234 03/07/2008
2 6789 26/06/2008
Assume I want information for AccountNumber 1234. The results I need are as follows:
ProductCode DeliveryDate Quantity
A1 03/07/2008 1
B2 03/07/2008 2
C3 NULL NULL
D4 03/07/2008 4
E5 NULL NULL
Assume I want information for AccountNumber 6789. The results I need are as follows:
ProductCode DeliveryDate Quantity
A1 NULL NULL
B2 NULL NULL
C3 26/06/2008 1
D4 NULL NULL
E5 26/06/2008 2
I know that I could get a datatable with a list of products and then write some code to loop through it and lookup Orders and OrderDetails to get the other two fields but I'd rather get all the information that I need in one call.
Any ideas how to go about writing the SQL Select statement?
Thanks,
dlarkin77
I have 3 tables : Products, Orders and OrderDetails.
The Products table has a ProductCode field
The Orders table has an AccountNumber, DeliveryDate field and an ID field
The OrderDetails has a foreign key that points to the ID field in Orders
The OrderDetails has a foreign key that points to the ProductCode in Products
I need to get a list of all ProductCodes
For each ProductCode I need to get the most recent Orders.DeliveryDate and OrderDetails.Quantity where Order.AccountNumber equals a given AccountNumber
For example assume each table contains the following records:
Products Table
ProductCode
A1
B2
C3
D4
E5
OrderDetails Table
ID ProductCode Quantity
1 A1 1
1 B2 2
1 D4 4
2 C3 1
2 E5 2
Orders Table
ID AccountNumber DeliveryDate
1 1234 03/07/2008
2 6789 26/06/2008
Assume I want information for AccountNumber 1234. The results I need are as follows:
ProductCode DeliveryDate Quantity
A1 03/07/2008 1
B2 03/07/2008 2
C3 NULL NULL
D4 03/07/2008 4
E5 NULL NULL
Assume I want information for AccountNumber 6789. The results I need are as follows:
ProductCode DeliveryDate Quantity
A1 NULL NULL
B2 NULL NULL
C3 26/06/2008 1
D4 NULL NULL
E5 26/06/2008 2
I know that I could get a datatable with a list of products and then write some code to loop through it and lookup Orders and OrderDetails to get the other two fields but I'd rather get all the information that I need in one call.
Any ideas how to go about writing the SQL Select statement?
Thanks,
dlarkin77