Joining the tables

[PatrickMast]

Hi @halil,

Can you try following steps:

1. Create a new Retool table to store the combined data.
2. Create a Retool query to populate the new table with the data from the five existing tables.
3. Use the CASE statement in the Retool query to map the old and new values from the log table to the corresponding values in the other tables.
4. Use the JOIN statement in the Retool query to combine the data from the different tables.

Here is an example of a Retool query that you can use to populate the new table with the combined data:

CREATE TABLE combined_data (
  id INT,
  table_name VARCHAR(255),
  old_value VARCHAR(255),
  new_value VARCHAR(255),
  employee_name VARCHAR(255)
);

INSERT INTO combined_data (
  id,
  table_name,
  old_value,
  new_value,
  employee_name
)
SELECT
  log.id,
  log.table_name,
  log.old_value,
  log.new_value,
  employees.name
FROM log
INNER JOIN employees ON log.employee = employees.id
INNER JOIN stations ON log.old_value = stations.id
INNER JOIN stations ON log.new_value = stations.id
INNER JOIN cities ON log.old_value = cities.id
INNER JOIN cities ON log.new_value = cities.id
INNER JOIN decision ON log.old_value = decision.id
INNER JOIN decision ON log.new_value = decision.id

This query will create a new table called combined_data that contains the following columns:

• id: The ID of the log entry.
• table_name: The name of the table that the log entry is associated with.
• old_value: The old value of the field that was changed.
• new_value: The new value of the field that was changed.
• employee_name: The name of the employee who changed the field.

The query will also join the log table with the employees, stations, cities, and decision tables to get the corresponding values for the old_value and new_value columns.

Once the new table has been populated with the combined data, you can use it to create reports and dashboards in Retool. For example, you could create a report that shows all of the changes that have been made to the station and city tables, or a dashboard that shows the number of changes that each employee has made.

Hope this helps.

Patrick