![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » Search Forums » Search Results » View Thread |
Messages 1 to 3 of 3 total |
![]() |
Tue, Dec 7 2010 4:30 PM | Permanent Link |
Dale Derix | Hello: How would you create a query of related table to create a single table for exporting to an ascii file For example, given the 4 tables below, each table is related to the Jobs table, but the other tables are not related to each other. Jobs table Contacts table Appointments table Document table The contents of the table would be something like this: Jobs Table (Fields: jobKey, JobName) j1, Misty Creek j2, Palm Island j3, Longwood Run Contacts: (fields: contactKey, JobKey, ContactName c1, j1, John Smith c2, j1, Tom Jones c3, j2, Jane Doe Appointments: (fields, AppointmentKey, jobkey, Appointment) a1, j1, Prepare sketch a2, j1, Lunch with client a3, j3, Some other appointment Documents: (fields, documentKey, jobKey, documentName) d1, j1, Document A d2, j1, Document B d3, j2, Document C The goal is to create a single table that would include all the records for say, Job 1 (Misty Creek). It would look something like this: j1, Misty Creek, John Smith, , j1, Misty Creek, Tom Jones, , j1, Misty Creek, , Prepare Sketch, j1, Misty Creek, , Lunch with Client, j1, Misty Creek, , , Document A j1, Misty Creek, , , Document B Is this even possible? Or is there a better approach to exporting data like this? XML Perhaps? Thanks Dale |
Tue, Dec 7 2010 8:49 PM | Permanent Link |
John Hay | Dale > How would you create a query of related table to create a single table for exporting to an ascii file > > For example, given the 4 tables below, each table is related to the Jobs table, but the other tables are not related to each other. > > > > Jobs table > Contacts table > Appointments table > Document table > > > The contents of the table would be something like this: > > > Jobs Table (Fields: jobKey, JobName) > j1, Misty Creek > j2, Palm Island > j3, Longwood Run > > > Contacts: (fields: contactKey, JobKey, ContactName > c1, j1, John Smith > c2, j1, Tom Jones > c3, j2, Jane Doe > > > Appointments: (fields, AppointmentKey, jobkey, Appointment) > a1, j1, Prepare sketch > a2, j1, Lunch with client > a3, j3, Some other appointment > > > Documents: (fields, documentKey, jobKey, documentName) > d1, j1, Document A > d2, j1, Document B > d3, j2, Document C > > > The goal is to create a single table that would include all the records for say, Job 1 (Misty Creek). > > > It would look something like this: > > j1, Misty Creek, John Smith, , > j1, Misty Creek, Tom Jones, , > j1, Misty Creek, , Prepare Sketch, > j1, Misty Creek, , Lunch with Client, > j1, Misty Creek, , , Document A > j1, Misty Creek, , , Document B > It looks like a union query, for example SELECT Jobs.Jobkey,Jobs.JobName,Contacts.ContactName,'' AS OtherField FROM Jobs JOIN Contacts ON Jobs.Jobkey=Contacts.JobKey WHERE JobKey=j1 UNION ALL SELECT Jobs.Jobkey,Jobs.JobName,'',Appointments.Appointment FROM Jobs JOIN Apointments ON Jobs.Jobkey=Appointments.JobKey WHERE JobKey=j1 UNION ALL SELECT Jobs.Jobkey,Jobs.JobName,'',Documents.Document FROM Jobs JOIN Documents ON Jobs.Jobkey=Documents.JobKey WHERE JobKey=j1 John |
Wed, Dec 8 2010 2:32 PM | Permanent Link |
Dale Derix | <<<<It looks like a union query, for example SELECT Jobs.Jobkey,Jobs.JobName,Contacts.ContactName,'' AS OtherField FROM Jobs JOIN Contacts ON Jobs.Jobkey=Contacts.JobKey WHERE JobKey=j1 UNION ALL SELECT Jobs.Jobkey,Jobs.JobName,'',Appointments.Appointment FROM Jobs JOIN Apointments ON Jobs.Jobkey=Appointments.JobKey WHERE JobKey=j1 UNION ALL SELECT Jobs.Jobkey,Jobs.JobName,'',Documents.Document FROM Jobs JOIN Documents ON Jobs.Jobkey=Documents.JobKey WHERE JobKey=j1 John >>>> Thanks John: I'll bet that will work just fine. Unfortunately, I just got pulled off this project for the next few days so I won't be able to try it out for a while. Dale |
This web page was last updated on Saturday, June 22, 2024 at 05:51 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |