1) This is mostly an SQL question, although MySQL may have some trick up its sleeve that would help me.
2) I've searched the archives, and google
3) I've been using SQL for a long time, but can't think of a way to solve this
4) This may not be possible. :)
I am dealing with serial data that is being put into a table, and I have to search through that data to find certain "start words." That is, data that indicates the start of a new packet of data. This start word, since this is asynchronous serial data, could be split over rows.
For purposes of example, let us assume we have a table of four columns, and that my start indicator is "strt" in one column and "word" in the next column. Now I want to find the next start word. The first three cases are easy, I just do something like WHERE col1 = 'strt' AND col2 = 'word', etc..
But, what I need to be able to do is something like this:
SELECT unique_key_field FROM table_name WHERE (col1='strt' AND col2='word') OR (col2='strt' AND col3='word') OR (col3='strt' AND col4='word') OR (col4='strt' AND col1_in_the_next_row='word')
Is this even possible? I'd hate to issue hundreds of queries to check if "strt word" is split across rows.
Should I investigate setting variables equal to the col4, and on a failed search, use that variable in the next query to see if the "old col4" pairs with anything in col1?
Or am I better off searching for the good case, and on failure, go and look for 'strt' in col4, then when I get a row, see if 'word' is in col1 on the next row (via another query)?
Ideas? Tips? Suggestions?
Not sure about the specifics of your application, but a problem like this makes me feel inclined to have the data in a different layout:
Something like this:
create table t1(row_id int, col_id tinyint, packet_id int, val varchar(50), unique key(row_id,col_id))
Then perhaps the following may work :
select a.packet_id from t1 a, t1 b where a.row_id = b.row_id and a.col_id = b.col_id + 1 and a.val = 'strt' and b.val = 'word' union select a.packet_id from t1 a, t1 b where a.row_id = b.row_id + 1 and a.col_id = 4 and b.col_id = 1 and a.val = 'strt' and b.val='word'
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
