[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: WHERE clauses across rows...
From:       "Joshua J. Kugler" <jkugler () bigfoot ! com>
Date:       2004-02-28 6:33:46
Message-ID: 200402272133.46742.jkugler () bigfoot ! com
[Download RAW message or body]

Oh. Yeah. Joins. Right.  Self Joins.  I've done joins, this just didn't occur 
to me.  Thanks for the tip.  I'll try it out.

j----- k-----

On Friday 27 February 2004 07:55 am, Eric B. wrote:
> Not sure how you determine what the order of your rows are, but assuming
> you have a column called rownumber, or soemthing to that extent, which is a
> sequential numbering of the rows in your table, you can probably do it with
> a join on itself.  You might have to play with the join syntax a little (in
> the where clause), b/c this is totally off the top of my head.
>
> ie:
> SELECT unique_key_field FROM table_name as t1, table_name as t2 where
> t1.rownumber = t2.rownumber+1
> and
> (
> (t1.col1='strt' and t1.col2='word')
> OR (t1.col2='strt' and t1.col3='word')
> OR (t1.col3='strt' and t1.col4='word')
> OR (t1.col4='strt' and t2.col1='word')
> )
>
> Good luck.
>
> Eric
>
>
> "Joshua J. Kugler" <jkugler@bigfoot.com> wrote in message
> news:200402261908.16054.jkugler@bigfoot.com...
>
> > 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?
> >
> > Thanks much!
> >
> > j----- k-----
> >
> > --
> > Joshua J. Kugler
> > Fairbanks, Alaska
> > Computer Consultant--Systems Designer
> > .--- --- ... .... ..- .-    -.- ..- --. .-.. . .-.
> > jkugler@bigfoot.com
> > ICQ#:13706295
> > Every knee shall bow, and every tongue confess, in heaven, on earth, and
>
> under
>
> > the earth, that Jesus Christ is LORD -- Count on it!
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
>
> http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

-- 
Joshua J. Kugler
Fairbanks, Alaska
Computer Consultant--Systems Designer
.--- --- ... .... ..- .-    -.- ..- --. .-.. . .-.
jkugler@bigfoot.com
ICQ#:13706295
Every knee shall bow, and every tongue confess, in heaven, on earth, and under 
the earth, that Jesus Christ is LORD -- Count on it!


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]