cornercuttin
10-19-2006, 11:04 PM
ok, this is hard to say in words, so i will just show you...
say i have 1 table and it has 4 fields, and looks basically like this:
CREATE TABLE mytable (my_id mediumint unsigned not null auto_incremen, num1 tinyint unsigned not null, num2 tinyint unsigned not null, num3 tinyint unsigned not null, primary key (myid))
now im looking for all of the my_id's where row x has number A in any of the num1, num2, or num3s, and then row x + 1 has number B in any of the num1, num2, or num3s.
so figuratively, say i was looking for 6 in row x and 10 in row x plus 1, i would be saying:
give me all records where in a row, any of the values equal 6, and then the next row contains the value 10
not including the my_id row.
do you get what i am asking for? i want the values where 6 appears in row x and 10 appears in row x +1. i think the auto_increments could be used here, but i am not sure how.
any help would be great. i am using php, and it is easy to get all rows where x = 6, then use a for loop and do a request for each "next row" to see if it contains the value 10, but i feel like this is a highly inefficient way of doing it. there has got to be a way that the SQL can handle all of it.
say i have 1 table and it has 4 fields, and looks basically like this:
CREATE TABLE mytable (my_id mediumint unsigned not null auto_incremen, num1 tinyint unsigned not null, num2 tinyint unsigned not null, num3 tinyint unsigned not null, primary key (myid))
now im looking for all of the my_id's where row x has number A in any of the num1, num2, or num3s, and then row x + 1 has number B in any of the num1, num2, or num3s.
so figuratively, say i was looking for 6 in row x and 10 in row x plus 1, i would be saying:
give me all records where in a row, any of the values equal 6, and then the next row contains the value 10
not including the my_id row.
do you get what i am asking for? i want the values where 6 appears in row x and 10 appears in row x +1. i think the auto_increments could be used here, but i am not sure how.
any help would be great. i am using php, and it is easy to get all rows where x = 6, then use a for loop and do a request for each "next row" to see if it contains the value 10, but i feel like this is a highly inefficient way of doing it. there has got to be a way that the SQL can handle all of it.