How to get data with joins from 2 tables?

mistercash60000

Hello,

well this is my problem:

I have a table tblusers:
pkuserID(auto_increment),
Username.

and a table tblfriends:

pkFriendID(auto_increment),
fkUserID,
FriendID.

the fkUserID is the user his id(the person that choses his friends)
and that user can chose his friends also from the table tblusers wich will be stored in FriendID.

The problem is that I want to chose all users from tblusers in a query that aren't already in my friendslist(tblfriends).

How can I do this?
I have tried left joins and also a left join with as where clause 'FriendID is null'.
This works for a part but if someone else chose a friend that I don't have chosen. It doesn't shows his name also. while I don't have his name in my friendslist.

I hope someone can help me.
thanks in advance,

Sxooter

I would think something like:

select pkuserid, username from tblusers where id not in (userid,(select friendid from tblfriends where fkuserid=userid));

should work.

mistercash60000

That doens't seem to work.
I thought that you couldn't use subqueries in mysql.

I have to select the data that is excluded from tblfriends that I already have in my friendslist.

Sxooter

I'm pretty sure that starting with 4.0 MySQL supports subselects.

I'm also pretty sure the name of this forum is database, and you didn't mention you were using MySQL, or which version.

Seeing as how newer MySQLs, and all other databases seem to support subselects, if you're using a really restricted database, you might wanna mention it before asking questions.

I'm not real familiar with all the ways to work around that in MySQL. And since I don't use a version that has the no subselect limit, I don't worry too much about it.

I think the mysql docs pretty much walk you through the equivalent of a not in() clause replacement though.

mistercash60000

I work with:
MySQL 3.23.58
so I think it doesn't support subqueries.
If it supported subqueries I would already tried it.
But I need a way around it.

Sxooter

So I assume you've read through the chapters on converting sub selects into joins, but that isn't working?

http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#Rewriting_subqueries

could you show us the query you have that doesn't (yet!) work?

mistercash60000

This is my sql query:
"SELECT UserID,Username,FriendsID from tblusers left join tblfriends on UserID=FriendsID where FriendsID is null

This gives some name names that I don't have in tblfriends.
But it gives all names that aren't used in tblfriends. Not just from the names that I have chosen. But all the names that aren't in the table tblfriends

If piet chose jan as friend then I mistercash can't chose jan as friend anymore do you get it.
Normally a person can have several friends. and those friends can have several other friends.

Sxooter

Does adding something like

and Userid='jan'

to it? Again, this isn't my area of expertise, so I'm just blinding shooting my shotgun hoping to hit something 🙂