DCJones DCJones - 2 months ago 7
MySQL Question

MySql: Returning one row of data from two records

I have a table where the recoards contain water temperature, one record for hot water and anoth record for cold water.

What I am trying to do is to construct a query that will return both records based on the unique id and only produce one row containing both the hot and cold water data.

I have read that this can be done using an INNER JOIN but as you may see below my query returns two rows, one for the hot water and another for the cold water.

Is it possible to produce one row containing both hot and cold water?

This is my query so far:

SELECT h.UniqueID, h.Room, h.AuditDate, h.AuditBy, h.SeqID, h.WaterHot, h.WaterCold
FROM NXLHR_Hist h
INNER JOIN NXLHR_Hist b
ON h.UniqueID = b.UniqueID
WHERE h.WaterHot < '50' OR h.WaterHot > '66.0' OR h.WaterCold > '20.0'
AND DATE(h.CompStamp) BETWEEN DATE('2015-09-08') AND DATE('2016-09-10')
ORDER BY h.Room ASC


Even if I use GROUP BY UniqueID I still get two rows.

Any help would be great and I thank you for your time in advance.

Regards.

Answer

Presumably, your intention is better served by this WHERE clause:

WHERE (h.WaterHot < 50 OR
       h.WaterHot > 66.0 OR
       h.WaterCold > 20.0
      ) AND 
      DATE(h.CompStamp) BETWEEN DATE('2015-09-08') AND DATE('2016-09-10')  

Note the parentheses. You seem to want any record where the water temperature is out of range during that time period. In addition, I assume the values are numbers. Don't use single quotes around numbers; it is misleading to people and can sometimes mislead the SQL optimizer.

Your conditions are parsed as:

WHERE h.WaterHot < 50 OR
      h.WaterHot > 66.0 OR
      (h.WaterCold > 20.0 AND
       DATE(h.CompStamp) BETWEEN DATE('2015-09-08') AND DATE('2016-09-10')  
      )

Actually, I would write this as:

WHERE (h.WaterHot < 50 OR
       h.WaterHot > 66.0 OR
       h.WaterCold > 20.0
      ) AND 
      h.CompStamp >= DATE('2015-09-08') AND
      h.CompStamp < DATE('2016-09-11')  

This version allows the engine to use an index on CompStamp.

The query you want probably then looks something like this:

SELECT h.Room, MAX(h.WaterHot), MIN(h.WaterHot), MAX(h.WaterCold)
FROM NXLHR_Hist h 
WHERE (h.WaterHot < 50 OR
       h.WaterHot > 66.0 OR
       h.WaterCold > 20.0
      ) AND 
      h.CompStamp >= DATE('2015-09-08') AND
      h.CompStamp < DATE('2016-09-11')  
 GROUP BY h.Room;
Comments