MySQL join to correlate 2 fields in one Table to different records in
second table
Relevant fields from each table:
Table 1 named crac_info: ACName, AmperageHistoryID, TemperatureHistoryID
Table 2 named julydata3: TimeStamp, HistoryID, Value
I need to correlate each AC's AmperageHistory with its TemperatureHistory
by TimeStamp (in a given minute, what are this unit's values for each?).
All values are stored in Table2, differentiated by HistoryID. The query's
purpose is to build a flat file for a BI software.
The Query I have so far gets me everything I need except the correlated
TemperatureHistoryID. My issues reside in getting the nested select\join
to make the correlation. No offense taken if I'm way off here either; I'm
the accidental DBA and I haven't gotten inside MySQLs head yet to know how
it thinks. I just need it to work sooner than my learning curve will let
me work it out on my own, and I need an efficient way to do it, so any
assistance is most welcome:
SELECT julydata3.TimeStamp AS TimeStamp, crac_info.ACName AS ACName,
julydata3.Value AS Amperage,
( SELECT historyDupe.value FROM julydata3 as historyDupe INNER JOIN
crac_info ON crac_info.history_ID = historyDupe.HISTORY_ID INNER JOIN
julydata3 on historyDupe.TIMESTAMP = julydata3.TIMESTAMP WHERE
historyDupe.TIMESTAMP = julydata3.TIMESTAMP
) AS Temperature
FROM julydata3 INNER JOIN crac_info ON julydata3.HISTORY_ID =
crac_info.AmperageHistoryID
No comments:
Post a Comment