I am in the process of migrating an old proprietary database to Zoho CRM. I am responsible for exporting the data from the old system. I used MySQL workbench to export all of the tables to CSV and then I can manipulate them somewhat in Excel or Access.
I think, however, there must be an easier way to do what I need. I have two main tables, one called "Clients" and one called "Actions". I need to export all clients from the client table that have actions in the action table from 2019 or later. The "Actions" table is just a table of actions and notes that were done for any of the clients. Both tables have numerous fields and I will need all of the data from all of the fields.
Both tables have a Client ID field, and a Case ID field. Most clients have a Client ID, but older clients have a Case ID. I want to get all of the records in the "Clients" table with records in the "Actions" table from 2019 and later. I also want to get all of the records in the "Actions" table that are from 2019 and later. The "Actions" table has a field called Last Modified Date which is the field to use to determine the year of that record.
So for example, if there is a record in the "Clients" table for John Doe and his Client ID is 246. In the "Actions" Table there many be many records for Client ID 246 some going back as far as 2009. If Client ID 246 has at least one record in the "Actions" table from 2019 or later, I want his "Clients" table record included in the export, and I need every record in the "Actions" table even those from before 2019.
However, if I have Joe Smith with Client ID 123 and all of his records in the "Actions" table predate 2019, I do not need any of his records from either table.
I hope I was able to explain this well. Is there a way to do this using the MySQL Workbench GUI, or will I have to write some sort of script. Either way, I will need some help setting this up.
Thanks in advance for any help.