SQL SELECT HELP

[Mahinda]

I have a database table where TAGNAMEID, DATETIME, TAGNAMEVALUE are the columns. The values in each columns may look as:

555, 6-14-2007 12:34:00, 23.51
666, 6-14-2007 12:34:00, 190.32
555, 6-15-2007 12:34:00, 21.31
666, 6-15-2007 12:34:00, 187.35
555, 6-16-2007 12:34:00, 32.37
666, 6-16-2007 12:34:00, 167.32

I want to do a query on this data to get values between two given DATETIME values for TAGNAMEIDs and feed into a graph as X and Y values. In other words X axis will have 555 values and Y will have 666 values. I can do SELECT TAGNAMEVALUE FROM TABLENAME WHERE (TAGNAMEID=555 OR TAGNAMEID=666) AND DATETIME < timestamp AND DATETIME > timestamp ORDER BY DATETIME

But I cannot feed it without checking for TAGNAMEID in a while loop in the program itself. Could someone shed some light on me.

If I do the table as DATETIME, TAGNAME555, TAGNAME666 getting the data the way I want can be done as SELECT TAGNAME555, TAGNAME666 FROM tableName WHERE DATETIME<timestamp AND DATETIME>timestamp.

Thank you

 

[PHV]

And what about something like this ?
SELECT X.DATETIME, X.TAGNAMEVALUE Xvalue, Y.TAGNAMEVALUE Yvalue
FROM tableName X INNER JOIN tableName Y
ON X.DATETIME = Y.DATETIME
WHERE X.TAGNAMEID = 555 AND Y.TAGNAMEID = 666
AND X.DATETIME < timestamp AND X.DATETIME > timestamp
ORDER BY X.DATETIME

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Mahinda]

PHV Thank you very much

 

[Mahinda]

If I add another column to the above given table as LOCATIONID, that means the values will be as:

20, 555, 6-14-2007 12:34:00, 23.51
20, 666, 6-14-2007 12:34:00, 190.32
20, 555, 6-15-2007 12:34:00, 21.31
20, 666, 6-15-2007 12:34:00, 187.35
21, 555, 6-16-2007 12:34:00, 32.37
21, 666, 6-16-2007 12:34:00, 167.32

Can I do the following:

SELECT X.DATETIME, X.TAGNAMEVALUE Xvalue, Y.TAGNAMEVALUE Yvalue
FROM tableName X INNER JOIN tableName Y
ON X.DATETIME = Y.DATETIME AND X.SITEID = Y.SITEID
WHERE X.SITEID = 20 AND Y.SITEID = 20
AND X.TAGNAMEID = 555 AND Y.TAGNAMEID = 666
AND X.DATETIME < timestamp AND X.DATETIME > timestamp
ORDER BY X.DATETIME

Thank you

 

[PHV]

You may do the above, provided you choose the right columns (LOCATIONID or SITEID ?)

 

[Mahinda]

Yes, it was a typo!

Lets say you have more than 2 TAGNAMEIDs (50 of them). Is this the only way to get all 50 with a single call?

Thank you again for all the replies.