Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
How to join several tables together for exporting as a single table. |
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 Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |