Click here to Skip to main content
15,919,613 members
Home / Discussions / Database
   

Database

 
AnswerRe: Apostrophe In Field-Value Problem Pin
Kjetil Svendsen6-Jul-08 22:35
Kjetil Svendsen6-Jul-08 22:35 
QuestionNeed tips for designing Database for multilingual websites (6 different languages) Pin
amistry_petlad5-Jul-08 4:46
amistry_petlad5-Jul-08 4:46 
Questionselect problem Pin
Stephan Hoppe4-Jul-08 12:24
Stephan Hoppe4-Jul-08 12:24 
AnswerRe: select problem Pin
leoinfo4-Jul-08 14:59
leoinfo4-Jul-08 14:59 
GeneralRe: select problem Pin
Stephan Hoppe5-Jul-08 6:13
Stephan Hoppe5-Jul-08 6:13 
QuestionHow to get date only from the table Pin
BalasubramanianK4-Jul-08 3:20
BalasubramanianK4-Jul-08 3:20 
AnswerRe: How to get date only from the table Pin
leoinfo4-Jul-08 3:50
leoinfo4-Jul-08 3:50 
QuestionTrouble joining between 3 tables Pin
dlarkin773-Jul-08 10:14
dlarkin773-Jul-08 10:14 
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
AnswerRe: Trouble joining between 3 tables Pin
Paul Conrad3-Jul-08 11:15
professionalPaul Conrad3-Jul-08 11:15 
AnswerRe: Trouble joining between 3 tables Pin
Blue_Boy3-Jul-08 22:39
Blue_Boy3-Jul-08 22:39 
QuestionRTF in Reporting services reports Pin
AlterD3-Jul-08 9:14
AlterD3-Jul-08 9:14 
QuestionHelp in get Time Pin
Senthil S3-Jul-08 4:08
Senthil S3-Jul-08 4:08 
AnswerRe: Help in get Time Pin
DerekFL3-Jul-08 4:39
DerekFL3-Jul-08 4:39 
AnswerRe: Help in get Time Pin
Blue_Boy3-Jul-08 22:43
Blue_Boy3-Jul-08 22:43 
Questiontrigger instead of identity Pin
Mr.Kode3-Jul-08 1:04
Mr.Kode3-Jul-08 1:04 
AnswerRe: trigger instead of identity Pin
DerekFL3-Jul-08 4:43
DerekFL3-Jul-08 4:43 
AnswerRe: trigger instead of identity Pin
DerekFL3-Jul-08 4:55
DerekFL3-Jul-08 4:55 
AnswerRe: trigger instead of identity Pin
leoinfo4-Jul-08 4:26
leoinfo4-Jul-08 4:26 
Questionbegin tran... commit and stored procedures/functions Pin
blakey4043-Jul-08 0:21
blakey4043-Jul-08 0:21 
AnswerRe: begin tran... commit and stored procedures/functions Pin
DerekFL3-Jul-08 4:52
DerekFL3-Jul-08 4:52 
GeneralRe: begin tran... commit and stored procedures/functions Pin
blakey4043-Jul-08 5:11
blakey4043-Jul-08 5:11 
QuestionUnable to view SQL Report with normal login Pin
Ankur.Bakliwal3-Jul-08 0:09
Ankur.Bakliwal3-Jul-08 0:09 
Questioninstallation Pin
Kissy162-Jul-08 23:50
Kissy162-Jul-08 23:50 
AnswerRe: installation Pin
John_Adams3-Jul-08 5:35
John_Adams3-Jul-08 5:35 
QuestionStored Procedure or Trigger for Autogenerate Colunm Pin
rrrriiizz2-Jul-08 20:45
rrrriiizz2-Jul-08 20:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.