Mysql get values from table with join (or and) ?

Hi i have the following tables

table buddies |id|b1|b2|status|

(note status = 0/1/2 pending/accepted/blocked) also b1 always is the userid sending the request.

table messages |id|userid|message|

table accounts |userid|name|email|

basically id like to show all messages from 'table messages' that have a buddies status on 1 and are my buddies knowing that in 'table buddy' my userid can be in either b1 or b2 (not in both at the sames time) depending if i request or buddy has requested .

hope im clear on this

thanks

Hi Marcel, You have already had a suitable reply that answers your question, but I would like to point out that this is a really bad database design. You would do better to have a table for BuddyLists, a table for Users and a table for UserBuddyList.

BL: BL_ID (int)
BL_ID=>1
BL_ID=>2

User: UserID (int), Name (char), dob (date) etc..
UserID=>1, Name=>"bob", dob=>2004-04-01
UserID=>2, Name=>"jim", dob=>1984-08-15
UserID=>3, Name=>"max" dob=>1971-06-25

UserBL: BL_ID (int) [FK to BL.BL_ID], UserID (int) [FK to User.UserID]
BL_ID=>1,UserID=>1
BL_ID=>1,UserID=>2
BL_ID=>2,UserID=>1
BL_ID=>2,UserID=>3

Bob is friends with Jim (BL_ID = 1)
Jim is friends with Bob (BL_ID = 1)
Bob is friends with Max (BL_ID = 2)
Max is friends with Bob (BL_ID = 2)

Max might know Jim because they both know Bob

Bob has 2 friends (count UserBL where UserID = 1)

You can extend this so that you can have more than 2 UserBLs for a BuddyList (i.e. Google circles)


Read up on 3rd Normal form - it is your friend when you are building databases