SELECT to get two records from the same table, differentiated by date, in one row

Question

I have a table in which I keep different meters (water meter, electricity meter), and in another table I save the readings for each meter. The structure of the table is as follows: Table of counters

MeterID | MeterType | MeterName

Indication table:

ReadingID | MeterID | Index | DateOfReading

Readings for the meter are read monthly. What I'm trying to do now is get the Meter information, the current read and the previous read in just one line. Therefore, if I had a request, the following line:

MeterID | MeterType | MeterName | CurrnetIndex | LastIndex

I have the following query:

SELECT Meter.MeterID, Meter.MeterType, Meter.MeterName, CurrentReading.Index, PreviousReading.Index
FROM Meters AS Meter
LEFT OUTER JOIN Readings AS CurrentReading ON Meter.MeterID = CurrentReading.MeterID
LEFT OUTER JOIN Readings AS PreviousReading ON Meter.MeterID = PreviouseReading.MeterID
WHERE CurrentReading.ReadingID != PreviousReading.ReadingID AND DIMESTAMPDIFF(MONTH, CurrentReading.DateOfReading, PreviousReding.DateOfReading)=-1

The problem is that I may not have the current reading or the previous one, or both, but I still need to get information about the meter. It is perfectly acceptable for me to get NULL columns, but I still need a row :)

Answer 1

:

   SELECT m.meterid,
          m.metertype,
          m.metername,
          current.index,
          previous.index
     FROM METER m
LEFT JOIN READING current ON current.meterid = m.meterid
                         AND MONTH(current.dateofreading) = MONTH(NOW())
LEFT JOIN READING previous ON previous.meterid = m.meterid
                          AND MONTH(current.dateofreading) = MONTH(NOW())-1

- MONTH WHERE, , ON.

Answer 2

, :

select  *
,       (
        select  Index
        from    Readings r2
        where   r2.MeterID = m.MeterID
                and DIMESTAMPDIFF(MONTH, r1.DateOfReading, 
                                  r2.DateOfReading) = -1
        ) as LastIndex
from    Meter m
left join
        Readings r1
on      r1.MeterID = m.MeterID

. , where:

WHERE  PreviousReading.ReadingID is null
       or 
       (
           CurrentReading.ReadingID != PreviousReading.ReadingID 
           and 
           DIMESTAMPDIFF(MONTH, CurrentReading.DateOfReading,
                         PreviousReding.DateOfReading) = -1
       )

Answer 3

, sql- - , . , . , , , .