Results 1 to 2 of 2

Thread: [RESOLVED] [MySQL] Getting Records Between Date Range from Multiple Tables

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Resolved [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?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  2. #2

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    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.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width