Page 1 of 1

Multiple table select problem

Posted: Fri Jun 06, 2003 5:55 am
by ILoveJackDaniels

Code: Select all

SELECT tooyoo_friends.username FROM tooyoo_session, tooyoo_friends where tooyoo_friends.user="ILoveJackDaniels" and tooyoo_friends.username!=tooyoo_session.username order by tooyoo_friends.username
There's the query I'm using to select data. I want to get a list of everyone (offline) in the 'friends' table that I have added as friends. So 'tooyoo_friends.user' must equal my name. I also want to pick the people that are offline, so their name must not be in tooyoo_session. So the above seems, to me, like it should be right.

However, instead of giving a list of each person in my friends list who is offline, it lists every person four or five times. Anyone online is listed one less time that the other, offline folk. My brain aches - anyone have any idea of where I've gone wrong here?

Posted: Fri Jun 06, 2003 8:09 am
by Stoker
add DISTINCT to the SELECT query and you're a a little closer (avoid cross-join).. but what are you joining the tables on? A relationship shouldn't be based on "Something NOT EQUAL Something" as it would usually cause everything to match..

I find it much easier to read a query that explicit lists the joins

SELECT myuser.username
FROM myuser
LEFT JOIN sesssion ON myuser.user_id = session.user_id
WHERE (NOT session.online OR isnull (session.online)) AND myuser.user_id=$my_id
ORDER BY username

Posted: Fri Jun 06, 2003 8:42 am
by ILoveJackDaniels
Ahhhh, there we go. Thanks Stoker. isnull() was what I was after with a join, thanks :)