hi,
i have a problem, in sql i need to combine each table and use case/end structure. by the i am not sure whether it will work. i thought i may find an easy solution with retool. basically i have 5 table (actually more ),
1st table is log and has id, table name, old value, new value, user name columns; old and new values are ids from 2nd, 3rd and 4th tables.
2nd table is stations and has id and station name,
3rd table is decision and has 2 row; id=1 value= accepted, id=2 value= rejected.
4th table is cities and has id and city name, for instance 1, london; 2 paris; 3 istanbul; 4 berlin etc
5th is employees and 2 columns id and name
in 1st table
id=1; tableName=stations; oldValue=12, newValue=4;employee=Thomas.
id=2; tableName=decision; oldValue=null, newValue=2;employee=Mike.
id=3; tableName=cities; oldValue=2, newValue=9;employee=Hans.
i want to bring the values of those number in my 1st table like
id=1; tableName=stations; oldValue=Mall, newValue=Cloud;employee=Thomas.
id=2; tableName=decision; oldValue=null, newValue=rejected;employee=Mike.
id=3; tableName=cities; oldValue=jakarta, newValue=Melbourne;employee=Hans.
Create a new Retool table to store the combined data.
Create a Retool query to populate the new table with the data from the five existing tables.
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.
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.
my db is mysql, and retool uses postgresql and i am getting relation error.
i have other tables, their values are shown in 1st table. their values are shown as expected instead of id. at the beginning developer created like this. when i see id instead value, i want to assign the value of that id in 1st table.
is it possible to change the id with its text value like => i have station query, decision query, and city query seperately, when in any lines i see those table name under 1st table table name column, then my condition will be triggered and then i change the id with its text value?
Yes, it is possible to change the ID with its text value in Retool, even if your database is MySQL and Retool uses PostgreSQL. You can do this using a Retool query and the CASE statement.
Here is an example of a Retool query that you can use to change the ID with its text value:
SELECT
log.id,
log.table_name,
CASE
WHEN log.table_name = 'stations' THEN (SELECT station_name FROM stations WHERE id = log.old_value)
WHEN log.table_name = 'cities' THEN (SELECT city_name FROM cities WHERE id = log.old_value)
WHEN log.table_name = 'decision' THEN (SELECT value FROM decision WHERE id = log.old_value)
ELSE log.old_value
END AS old_value_text,
CASE
WHEN log.table_name = 'stations' THEN (SELECT station_name FROM stations WHERE id = log.new_value)
WHEN log.table_name = 'cities' THEN (SELECT city_name FROM cities WHERE id = log.new_value)
WHEN log.table_name = 'decision' THEN (SELECT value FROM decision WHERE id = log.new_value)
ELSE log.new_value
END AS new_value_text,
employees.name
FROM log
INNER JOIN employees ON log.employee = employees.id
This query will select 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_text: The text value of the old value.
new_value_text: The text value of the new value.
employee_name: The name of the employee who changed the field.
The CASE statement is used to map the old_value and new_value columns to the corresponding text values from the stations, cities, and decision tables.
Once you have created this query, you can use it to populate a new table with the combined data. Then, you can use the new table to create reports and dashboards in Retool.
Here are the steps on how to use the query:
Create a new Retool table to store the combined data.
Create a new Retool query and paste the query above into it.
Set the query's destination to the new table that you created in step 1.
Run the query.
The new table will be populated with the combined data, with the ID values replaced with their text values.
),