JOIN in friends system

[Adam]

I'm in the process of creating a friends system for my site. I'm really stuck trying to figure out how I could select the user info for each of the friends using JOIN.

 

I have the db table setup like this:

 

user_a | user_b | confirm

 

 

--- MOD ---

 

I've got a little closer, I now have the query:

 

SELECT DISTINCT t1.*, t2.*
FROM spawnusers AS t1, spawnfriends AS t2
WHERE t1.userid = t2.user_a
OR t1.userid = t2.user_b

 

This though returns everything twice, for example, if 'user_a' equaled 1 and the 'user_b' equaled 2, then when it gets to 'userid' (from the user table) 1 it will select it, and when it gets to 'userid' 2 t will select it. DISTINCT doesn't seem to be working?

 

Idea of it is so i can then run a php script which prints out which connections are confirmed and which aren't.

 

 

 

Can anybody help? Cheers!

[toplay]

Well, you didn't specify a column to be distinct by...you have "DISTINCT t1.*" which is saying every column. Specify one column instead. I don't really understand what you're trying to do but it sounds like you could use "GROUP BY t1.userid" at the end of your query.