Search code examples
mysql

Mysql Create a query with variables and functions

Hello everyone I have 3 requests each of which depends on each other, in the end there should be an automatic download of the archive from the table, and so we have:

We take the wims_detections table, it has a column "id" (let's assume I have it), we access the column "detection_id" we take and remember this identifier "TY-LU-W1_20231221T155059823+0300", then we go to the table sensors_detections_data we find the value from the column data_id by this detection_id and go to the third table sensors_data there by data_id we find the file in the column "data" and download it.

If you distribute it manually, it will look like this, but you need to do it automatically, specify the id and get a file from data at the output

SELECT detection_id FROM wims_detections WHERE id = '715696';

SELECT data_id FROM sensors_detections_data WHERE detection_id = 'TY-LU-W1_20231221T155059823+0300';

SELECT * FROM sensors_data WHERE data_id = 'TY-TY-SL1_20231221T155059878+0300';

enter image description here

Solution

  • You can assign session variables in the queries:

    SELECT @det_id := detection_id FROM wims_detections WHERE id = '715696';

SELECT @data_id := data_id FROM sensors_detections_data WHERE detection_id = @det_id;

SELECT * FROM sensors_data WHERE data_id = @data_id;

    But the normal way to do this is with a join:

    SELECT sd.*
FROM sensors_data AS sd
JOIN sensors_detection_data AS sdd ON sd.data_id = sdd.data_id
JOIN wims_detection AS wd ON sdd.detection_id = wd.detection_id
WHERE wd.id = '715696';