Multiple table select problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ILoveJackDaniels
Forum Commoner
Posts: 43
Joined: Mon May 20, 2002 8:18 am
Location: Brighton, UK

Multiple table select problem

Post 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?
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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
User avatar
ILoveJackDaniels
Forum Commoner
Posts: 43
Joined: Mon May 20, 2002 8:18 am
Location: Brighton, UK

Post by ILoveJackDaniels »

Ahhhh, there we go. Thanks Stoker. isnull() was what I was after with a join, thanks :)
Post Reply