Complex query

newtomysql · July 7, 2010, 3:02am

Dear All,
I have query below as below. The first query give me all the device data together ordered based on tblDevice.dateTime Desc.

“SELECT * from tblDevice order by tblDevice.dateTimer Desc”

The problem is that for each of the device table I have to check another table called as tblMessage. Then I have to check if there is any message in that which have the Date timer value more than the tblDevice then I have to pick that to be shown. Now what I do is first I run the first loop based on tblDevice then I do a logic in the loop run the second query. All works fine the only problem I can not maintain the time sequence. Say for instance 1st data for tblDevice is on 2010-10-20:10:10. Then second data from tblDevice is 2010-10-20:10:05. But for the second data there is a matching data in the tblMessage with the timestamp 2010-10-20:11:00. So when I want to show the data in html table suppose my second data should be first shown but I cannot control that now cause I am constraint based “order by tblDevice.dateTimer Desc”. Any idea please?

“Select * From tblMessage WHERE deviceId”.$dID." and DATE>‘“.$currentDateTimer.”’ order by DATE DESC"
//$dID and $currentDateTimer is from tblDevice when I run the loop.
Thank you.

r937 · July 12, 2010, 8:41pm

my idea is to have atomic data

storing four values (000021101000,003FF00,8750.53,12667) inside a single text column violates this idea

(it is called first normal form)

use four separate columns

r937 · July 12, 2010, 4:10pm

now you appear to be searching for an id inside the text value

in fact, based on your sample data, you probably want to find the serialNumber inside the text, not the deviceID

i think perhaps you should redesign the tables, because that LIKE comparison is what’s slowing it down

r937 · July 12, 2010, 5:40am

here’s your problem right here –

LEFT OUTER
JOIN tblMessage
ON [COLOR="Red"]tblMessage.deviceId [/COLOR]like[COLOR="red"] '&#37;tblDevice.deviceId%;[/COLOR]

first of all, tblMessage does not have a column called deviceId

then, the string '%tblDevice.deviceId%; is missing its closing single quote

also, that’s not how you check if one column is inside another, you would want to use LIKE CONCAT(‘%’,tblDevice.deviceId,‘%’)

but there are even further problems

if both tables have an auto_increment, how are you ever going to ensure that you get the right rows matching? what if there is more than one message for a device? then you’re screwed

als, inside the IF expression, you are comparing a DATETIME column value with a VARCHAR column value – i suggest you change the VARCHAR column to a DATETIME column to avoid conversion errors and incorrect results

my advice is to clean up your tables, then clean up your query, and don’t forget to test your query outside of php first

newtomysql · July 12, 2010, 4:01pm

Dear R937,
I have modified the query and also modified the tblMessage.DATE to datetime . It works but take a while just for small data and also I find too many redundant data.

SELECT tblDevice.deviceId
, IF(tblDevice.dateTimer > tblMessage.DATE, tblDevice.dateTimer, tblMessage.DATE) AS max_dateTimer
FROM tblDevice
LEFT OUTER
JOIN tblMessage
ON tblMessage.TEXT LIKE CONCAT(‘%’,tblDevice.deviceId,‘%’)

ORDER
BY IF(tblDevice.dateTimer > tblMessage.DATE, tblDevice.dateTimer, tblMessage.DATE) DESC

r937 · July 7, 2010, 3:26pm

either CASE (standard) or GREATEST (proprietary)

neither of those require GROUP BY, though

DarthGuido · July 7, 2010, 3:23pm

What? That ain’t possible? Darn…
I guess I was too much in a hurry, and mixed the two concepts in my mind. Would be nice if it was possible, though.

The OP wants the max date for each deviceId, but that date can be in any of two tables. I guess a CASE is the way to go.

BrandonK · July 7, 2010, 2:32pm

Rudy, would it be too expensive to use a conditional function like IF()? Also, COALESCE() would only work if tblMessage is always newer than tblDevice whenever it exists.

SELECT tblDevice.deviceId
     , IF(tblDevice.dateTimer > tblMessage.dateTimer, tblDevice.dateTimer, tblMessage.dateTimer) AS max_dateTimer
  FROM tblDevice
LEFT OUTER
  JOIN tblMessage
    ON tblMessage.deviceId = tblDevice.deviceId
 ORDER
    BY IF(tblDevice.dateTimer > tblMessage.dateTimer, tblDevice.dateTimer, tblMessage.dateTimer) DESC

(untested)

r937 · July 7, 2010, 3:00pm

there’s nothing wrong with using IF, except i would always use CASE instead, since IF is proprietary to mysql – i prefer using standard sql whenever possible, i.e. whenever the database system supports it, and mysql certainly supports CASE

COALESCE is really only appropriate when one of the expressions can be NULL, which is often the case with LEFT OUTER JOINs

as for the message dates in this thread, i’m afraid i haven’t taken the time to unravel what’s going on, i just happened to see guido’s rather novel use of MAX

newtomysql · July 7, 2010, 2:17pm

Dear All,
Below is my create table. Both my tblDevice and tblMessage is below.
In the earlier explanation actually I made a mistake to match with the tblMessage below is how I suppose to do.
“Select * From tblMessage WHERE TEXT like '%$dID%‘and DATE>’”.$currentDateTimer."’ . So based on the sample data below deviceID=104 should be first shown before deviceID=103. Thank you.

CREATE TABLE tbldevice (
deviceID int(11) NOT NULL AUTO_INCREMENT,
serialNumber varchar(24) NOT NULL,
message varchar(24) NOT NULL,
dateTimer datetime NOT NULL,
PRIMARY KEY (deviceID,serialNumber),

) ENGINE=MyISAM AUTO_INCREMENT=10216 DEFAULT CHARSET=latin1

CREATE TABLE tblmessage (
ID int(11) NOT NULL AUTO_INCREMENT,
TEXT varchar(200) NOT NULL,
DATE varchar(50) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=14521 DEFAULT CHARSET=latin1

Sample data for tblDevice

103,12589,6643292,2010-07-07 19:20:20
104,12667,6628244,2010-07-07 19:15:20

Sample data for tblMessage

1015,“000021101000,003FF00,8750.53,12667”,2010-07-07 19:21:23

r937 · July 7, 2010, 11:51am

guido, wtf???

MAX(tblDevice.dateTimer, tblMessage.dateTimer)

did you intend to use COALESCE somewhere in there?

DarthGuido · July 7, 2010, 7:43am


SELECT 
    tblDevice.deviceId 
  , MAX(tblDevice.dateTimer, tblMessage.dateTimer) AS maxtime
FROM tblDevice
LEFT OUTER JOIN tblMessage 
ON tblDevice.deviceId = tblMessage.deviceId 
GROUP BY tblDevice.deviceId

SpacePhoenix · July 7, 2010, 5:59am

Can you show a CREATE TABLE for both tables along with some sample data for each?

newtomysql · July 12, 2010, 7:10pm

Dear R937,
Yes you are right I am looking for the serial number in the message. So how shall I redesign the table what is your idea? Thank you.

newtomysql · July 12, 2010, 2:21am

Dear R937,
Below is my create table and sample data and followed by the query

CREATE TABLE tbldevice (
deviceID int(11) NOT NULL AUTO_INCREMENT,
serialNumber varchar(24) NOT NULL,
message varchar(24) NOT NULL,
dateTimer datetime NOT NULL,
PRIMARY KEY (deviceID,serialNumber),

) ENGINE=MyISAM AUTO_INCREMENT=10216 DEFAULT CHARSET=latin1

CREATE TABLE tblmessage (
ID int(11) NOT NULL AUTO_INCREMENT,
TEXT varchar(200) NOT NULL,
DATE varchar(50) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=14521 DEFAULT CHARSET=latin1

Sample data for tblDevice

103,12589,6643292,2010-07-07 19:20:20
104,12667,6628244,2010-07-07 19:15:20

Sample data for tblMessage

1015,“000021101000,003FF00,8750.53,12667”,2010-07-07 19:21:23

SELECT tblDevice.deviceId
, IF(tblDevice.dateTimer > tblMessage.dateTimer, tblDevice.dateTimer, tblMessage.dateTimer) AS max_dateTimer
FROM tblDevice
LEFT OUTER
JOIN tblMessage
ON tblMessage.deviceId like '%tblDevice.deviceId%;
ORDER
BY IF(tblDevice.dateTimer > tblMessage.dateTimer, tblDevice.dateTimer, tblMessage.dateTimer) DESC

r937 · July 11, 2010, 10:49am

which query? could you show it?

newtomysql · July 9, 2010, 3:20pm

Dear All,
This query it takes too long and “max_dateTimer” is always null so what is the best solution?Thank you.