Joining 3 MySQL Tables with data comparison

I have 3 MySQL tables currently in my database and I was trying to work out how to join them all together and compare data between them.

The queries form part of a "Friend or Foe" system I'm currently working on as a side project in my spare time, but I cannot for the life of me work it out. I've successfully managed a 2 table join but am getting nowhere with a 3 table join.

Anyway, here is the table layout.

Table 1

Unique ID | Username | Password | Activity
1         | SomeUser | password | Active
2         | NewUsers | password | InActive
3         | GuestUse | password | Active

Table 2

FileID    | UploadedBy | Type | FileName | Description
1         | SomeUser   | MP3  | Demo.mp3 | Bass Guitar Riff
2         | SomeUser   | MP4  | Demo.mp4 | Some Youtube Video

Table 3

ListOwner | Friends  | Foes
SomeUser  | GuestUse | NULL
GuestUse  | SomeUser | NULL

All I'm trying to achieve is that the "ListOwner" can view files uploaded by users who are in his/hers "friends" list, naturally there would be a page where you could view all files uploaded, but this is more designed towards seeing uploads of people you appreciate more.

In essence I am trying to get the query to read the; "Username" from Table1, "UploadeBy" from Table2 and Everything from Table3

Example: GuestUse logs in, the query takes this information and compares his Username against Table 3 and then shows ONLY the uploads in Table2 from those who are in his friends list.

For structure the DB has the following setup.

Unique ID & Field ID are both INT(4)
Username is VARCHAR(42)
Password is VARCHAR(30)
Activity is VARCHAR(8)
ListOwner is VARCHAR(42) < Same as Username
Friends & Foes are TEXT

Any help you can give would be greatly appreciated.

Solution

Table 2 field "uploadedby" should be the numeric user id.

FileID    | UploadedBy | Type | FileName | Description
1         | 1          | MP3  | Demo.mp3 | Bass Guitar Riff
2         | 1          | MP4  | Demo.mp4 | Some Youtube Video

You should create one table for "friend" relation and one for "foe" relation.

Table 3: friends
ListOwnerId | FriendId
1           | 2
1           | 3
2           | 1

Table 4: foes
ListOwnerId | FoeId
3           | 2

Then proceed with queries from inner to outer.

SELECT friendid FROM friends WHERE listowner=$loginid;

This will extract all friends for $loginid user.

If $someuseris is in $loginid friends list but $someuserid set $loginid as a foe, should $loginid see $someuserid files? Think of that. In the meanwhile, foes table is useless.

Now, select all files from friends:

SELECT * FROM files WHERE uploadedby IN (SELECT friendid FROM friends WHERE listowner=$loginid)

This query will give you a list of desired files.

This is done with a subquery, you can do it also with a join.

SELECT files.* FROM files JOIN friends ON files.uploadedby=friends.friendid WHERE friends.listowner=$loginid;