MySQL Join problem

[Anon]

There's my 2 tables:
Users:
IDUser | FullName
1 | Name1 LastName1
2 | Name2 LastName2
3 | Name3 LastName3
4 | Name4 LastName4

and Users_Activities:
IDActivity | IDUser
1 | 1
1 | 3

So Users 1 and 3 haves Activity n° 1

Now I want to download the list of users in the USERS table excluding the selected in the USERS_ACTIVITES table..

What's the query?
Mine is:
SELECT DISTINCT Users.IDUser, Users.FullName
FROM Users, Users_Activities
WHERE Users.IDUser <> Users_Activites.IDUsers

but it returns me all rows.. what should I use?

Thanks in advance
Alan
P.S. Sorry for my english

[Anon]

I believe you're trying to go for more of an outer join.

select u.iduser,u.fullname
from users u
left outer join users_activities ua on ua.iduser = u.iduser
where ua.idactivity is null

This will return all users which do not have a record in the user_activity table (the activity id is null).

Kelly

[[deleted]]

What you want is a classic example of LEFT JOIN.

http://www.mysql.com/doc/J/O/JOIN.html

If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:
mysql> select table1.* from table1
LEFT JOIN table2 ON table1.id=table2.id
where table2.id is NULL;

[Anon]

Thanks Kelly..
That worked great..

Bye 🙂
Alan

[Anon]

Thanks.. that's the same answer as kelly 🙂

Bye
Alan

[Anon]

I'm watching again these lines..
I want to list non-selected users but only if i specify the IDActivity..

I.E. If i have 2 users assigned in acivity '1' and i want to add other users i must list only non-added users.. but if I need to add an user in another activity i want to be able to select all users.. so that WHERE IDActivity IS NULL doesn't work anymore for me..

Thanks in advance 🙂)
Alan

[[deleted]]

Allmost, she used an LEFT OUTER JOIN, where I just used a LEFT JOIN.

(but I do have to refresh the page more often before posting, I could have saved myself some time :-) )

[[deleted]]

The query kelly posted has:

"where ua.idactivity is null"

which means that the idactivity from the second table should be NULL, meaning that there is no record for that userid.

But there is nothing stopping you from replacing "IS NULL" with "<>1"

"where ua.idactivity <> 1"

That will give you all the people who do not have a mtching record with idactivity is 1.
They can still have records for idactivity=6, or 764576, or even NULL, but you will not get those who have a matching record with idactivity=1

[Anon]

SOrry vincent..
I want to get all people don't added but that is assigned to activity 1
WHERE ua.idactivity = '1' doesn't work..
What should I use?

Thanks
Alan

[[deleted]]

sorry, I don't understand it anymore :-)

can you explain it again please?

[Anon]

Take the 2 tables described in the 1st post then take this description 🙂

I want to list All users EXCEPT the ones inserted in the second table.. and you'r first query was excellent..
the problem is that I want to select all users except the selected inthe second table ONLY for activity 1..
I.E.
- First table: User1,2,3,4,5
- Second Table: User1,4 Activity 1 and User2,5 Activity 2
- To select: Users in first table except Users in second table where Activity = 1.. so Results must be User2,3,5

Understand ? 🙂)

Alan

[[deleted]]

Ok, then you want to do the LEFT JOIN, plus
WHERE idactivity IS NULL (to get all the users that are not linked to an activity)
OR idactivity <> 1 (to get all the users who are linked to an activity, but not to activity 1)

WHERE idactivity IS NULL OR idactivity <> 1

[Anon]

No! I need to Exclude all users linked to activity 1 from the full list! 🙂)

Alan

[[deleted]]

Did you try it?

[merlin747]

Actually, I am a he. 🙂

Kelly

vincent wrote:

Allmost, she used an LEFT OUTER JOIN, where I just used a LEFT JOIN.

(but I do have to refresh the page more often before posting, I could have saved myself some time :-) )

[merlin747]

Alan, I agree with vincent here. The where clause he described will get you everything from users except ones who have activity 1. Unless someone changed the rules on us. 🙂

Did it not work for you?

kelly

vincent wrote:

Did you try it?

[Anon]

... restart from the begin
I want to WORK with activity 1.. not want to exclude it!!
I want to exclude USERS!

Table1.users -(minus) Table2.selectedusers WHERE Activity = 1

So if table 1 is:
User1, User2, User3, User4, User5

and Table 2 is:
Activity 1, User1
Activity 1, User3

Result should be:
User2, User4, User5 <-- that are "free" users for Activity 1!

If i use another activity (i.e. 2) that isn't inserted in the 2nd table the User listing is full.. eg all users!

aaaaarghh!! 🙂))

Alan

Again.. sorry for my english 🙂))

[[deleted]]

lol

where are the days when boys where called John and girls where called Shirley.. :-)

[[deleted]]

let me rephrase my question:

Did you try it?

[Anon]

Yeah I did try it!!
but it doesn't work!
What i need is to compare table1 users with table2 users that ARE LINKED TO AN ACTIVITY!! (1) The IDActivity field is only to table 2!

Table1:
UserID | FullName
1 | User1
2 | User2
3 | User3
4 | User4
5 | User5

Table2:
ActivityID | UserID
1 | 1
1 | 2
2 | 1
2 | 3

Now i need to add a user for activity 1 and i want the list of selected people:

SELECT table1.UserID, table1.FullName
FROM table1
INNER JOIN table2 ON table2.UserID = table1.UserID
WHERE ActivityID = '1'

and i want a list of "free" (not selected) people to assign to activity 1.. something like:

SELECT table1.UserID, table1.FullName
FROM table1, table2
WHERE table1.UserID <> table2.UserID
AND ActivityID = '1'
(Don't works..)

The result should be a list of users that aren't assigned to activity 1!
So
UserID | FullName <-- where activityID = 1
3 | User3
4 | User4
5 | User5

UserID | FullName <-- where activityID = 2 (example)
2 | User2
4 | User4
5 | User5

I can't explain better than this..
Alan