Mysql query question

[Masali]

I have a table named q_mails that has six cols:
id - to_nick - from_nick - threadid - left_folder - right_folder

id is a counter, to_nick is a username, from_nick is also a username, threadid contains a number and two user ids, like this: 908723487|user1|user2

in col threadid, user1 "owns" col left_folder and user2 "owns" right_folder. I don't know which user in threadid that will be first in the "list", ssometimes the threadid will look like: 908723487|user2|user1

I want a query that looks like:

select * from q_mails where to_nick='user1' and left_folder="trash"

or

select * from q_mails where to_nick='user2' and right_folder="trash"

the right_folder or left_folder column in the query should be set depending on where the user is located in col threadid, on the left or the right...

Is it possible to do this with only one query?? I you don't understand what I mean, ask for more clarification please

 

[tshot]

i don't know why u have kept threadid as a concatenated string. U can add 2 fields namely l_user and r_user and simply ur query
Else use 2 queries.

Following query can get left and right from your concatenated field. And we can assign it to a user variable with @var:= syntax in the same SELECT. But we cannot use it for testing in the same clause. For MySql
The general rule is to never assign and use the same variable in the same statement.

select @str:='908723487|user2|user1' ,substring_index(@str, '|', -2) as 'Just to show',
@lft:=substring_index(substring_index(@str, '|', -2), '|', 1) as Lft,
@rit:=substring_index(@str, '|', -1) as Rit ;

We cannot check the left and right variables in same select. For that we require next select statement (in same session)




----------------
ur feedback is a very welcome desire