I am building a site where members can create profiles that other members can view. I want to add a favourites feature to the profiles so a member can keep a list of their favourties members. This all works fine but now I want to find a way that a members can see ALL the members on the site and can see if they have already added them to their favourites.
I have two tables; members and favourites.
They look something like this but I've simplified them and this is the query I'm using:
SELECT members.*, favourites.* FROM `members` LEFT OUTER JOIN `favourites` on members.username = favourites.user_name WHERE members.username
Members
userid----------------username-------
1 Bob
2 David
3 Sue
Favourites
userid-----------------------user_name-----------------------------favoured_user
1 Bob Sue
3 Sue David
2 David Bob
I have been trying to do this by using an outer join on members.username and favourites.user_name but I get duplicate entries for members that have selected more than one favourite (this I believe is how an outer join should behave) but I want to retrieve all members from the table and only show which members have been favoured. I hope I'm making myself clear.
Thanks,
Julian