-
Oct 26th, 2021, 06:13 PM
#1
[RESOLVED] [MySQL] Getting Records Between Date Range from Multiple Tables
I have the following schema:
Code:
CREATE TABLE BloodPressure (
BloodPressureId int(11) NOT NULL AUTO_INCREMENT,
ReadDate date NOT NULL,
ReadTime time NOT NULL,
Systolic int(11) NOT NULL,
Diastolic int(11) NOT NULL,
Pulse double NOT NULL,
CONSTRAINT PK_BloodPressure_BloodPressureId PRIMARY KEY (BloodPressureId)
);
CREATE TABLE BloodSugar (
BloodSugarId int(11) NOT NULL AUTO_INCREMENT,
ReadDate date NOT NULL,
ReadTime time NOT NULL,
Reading int(11) NOT NULL,
CONSTRAINT PK_BloodSugar_BloodSugarId PRIMARY KEY (BloodSugarId)
);
CREATE TABLE Weight (
WeightId int(11) NOT NULL AUTO_INCREMENT,
ReadDate date NOT NULL,
ReadTime time NOT NULL,
Weight decimal(10,1) NOT NULL,
CONSTRAINT PK_Weight_WeightId PRIMARY KEY (WeightId)
);
Fiddle: http://sqlfiddle.com/#!9/cf8df4
What I need to do is generate a report where it will get relevant data from these records between a date range. This would be a sample result set:
ReadDate |
ReadTime |
Reading |
2021/10/26 |
1:23 |
120 / 70 |
2021/10/26 |
2:34 |
90 |
2021/10/26 |
3:35 |
175 |
Where the first record is from the BloodPressure (concatenating systolic, space, slash, space, diastolic), the second record is from BloodSugar (just reading), and the third record is from Weight (just weight). While all three records have different time and values, they all have the same date.
At first, I thought about creating a temporary table that would select from the tables performing a union, passing my date range:
Code:
CREATE TEMPORARY TABLE UniqueDates (ReadDate DATE);
INSERT INTO UniqueDates (ReadDate)
SELECT ReadDate FROM BloodPressure WHERE ReadDate BETWEEN @0 AND @1
UNION
SELECT ReadDate FROM BloodSugar WHERE ReadDate BETWEEN @0 AND @1
UNION
SELECT ReadDate FROM Weight WHERE ReadDate BETWEEN @0 AND @1;
SELECT * FROM UniqueDates;
DROP TEMPORARY TABLE UniqueDates;
But then if I do that I think I would need to create a loop to go over the date values and then do a select into based on the currently iterated value.
Is there a way that I can do this without having to implement a loop? In other words, can I create a SELECT query to perform this task?
-
Oct 26th, 2021, 07:41 PM
#2
Re: [MySQL] Getting Records Between Date Range from Multiple Tables
I don't know why I was overthinking it:
Code:
SELECT * FROM
(SELECT ReadDate, ReadTime, CONCAT(Systolic, ' / ', Diastolic) AS VALUE FROM BloodPressure
UNION ALL
SELECT ReadDate, ReadTime, Reading AS VALUE FROM BloodSugar
UNION ALL
SELECT ReadDate, ReadTime, Weight AS VALUE FROM Weight) AS subselect
WHERE ReadDate BETWEEN @0 AND @1
ORDER BY ReadDate
Last edited by dday9; Oct 26th, 2021 at 08:45 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|