Context: I have two tables.
Table1: Columns(id,location_name)
Table2: columns(id, data1, data2, data3)
Requirement:
I want to give multi selection option on the Grafana dashboard using table 1, where only location names would be listed.
When you select one ore more location, then the corresponding IDs are selected which can be interpolated on the panels to get the data for those IDs.
Things I have done:
Created two variables.
Variable 1: location_name
SELECT location_name FROM Table1
The preview lists all the names.
Selected the “Multi-value” option.
Variable 2: location_id
The preview returns all the corresponding values of the selected location names.
SELECT id FROM Table1 WHERE location_name IN ($location_name)
Panel Query:
SELECT DISTINCT id FROM Table2 WHERE id IN ( $location_id )
The above only returns 1 value.
Where as the same query directly on the MySQL DB, returns two values when I populate the ( $location_id ) with ids. Example:
SELECT DISTINCT id FROM Table2 WHERE id IN ( '403066','242034' )
Not sure what am I doing wrong! Can someone help?
Note: Everything is a string.
It looks like you don’t actually need second variable, and simply want location to de shown as string for user to select, but proceed with id once user selected something. If this is the case, look a the point 4 in this manual. Simple add aliases to your columns.