simple join query with <> sign not working 2

[zeeshan12]

Hey

i'm using mysql 3.23 for my DB. I am trying to run a simple mysql query with a join. Although i'm normally good at joins but for some reason i cant get this query to work and its just a simple join.

right have a users table

USERS
user_id int
user_name varchar(200)

USER_REMOVE_SEARCH
user_id int
rem_user_id int

i just want all users from users table except 7 the one's that user_id 7 deleted from his search and now exist in user_remove_search table

this is the query that i'm using


select distinct u.* from users u, user_remove_search urs where u.user_id<>7 AND (urs.user_id=7 and u.user_id != urs.rem_user_id)


when i run the query is shows me all the results from users table including the one's that exist in user_remove_search table again user_id 7.

anyone got any idea why this is happening?

 

[rac2]

Maybe a JOIN is not the answer.

This should yield all of the users who are not on 007's remove list.

select u.* 
from users u 
where u.user_id NOT IN (
       SELECT urs.rem_user_id
       FROM user_remove_search urs
       WHERE urs.user_id = 7
      )

 

[zeeshan12]

I wish i could run this query but unfortunately i'm running on an old version of mysql 3.23. It doesnt allow sub queries.

Any other suggestions.??


Thanks

 

[TonyGroves]

How about:
[tt]
SELECT u.*
FROM
users u
LEFT JOIN user_remove_search urs USING (userid)
WHERE usr.user_id<>7 OR usr.user_id IS NULL
[/tt]

 

[zeeshan12]

hey Tony,

I have tried this query but it didnt work either...this query's been on my nurves all weekend and i still cant sort it...

btw, your query required a little change with table names so here's the changed query.


SELECT u. *
FROM users u
LEFT JOIN user_remove_search urs
USING ( user_id )
WHERE urs.user_id <>7
OR urs.user_id IS NULL
LIMIT 0 , 30



Thanks

 

[r937]

SELECT u.*
FROM users u
LEFT
JOIN user_remove_search urs
ON urs.user_id = u.user_id
and urs.user_id = 7
WHERE urs.user_id IS NULL

i think we're having a little trouble properly understanding what you meant by this statement:

all users from users table except 7 the one's that user_id 7 deleted from his search and now exist in user_remove_search table

perhaps you could show us a few rows of sample data wich illustrate the situation

r937.com | rudy.ca

 

[zeeshan12]

i'm sorry if i didnt make it clear enough. I'll give it another go.

okay lets say USERS table have got these entries in it

USER_ID USER_NAME
1 zeeshan
2 jack
3 martin
4 kathrine


and following entries in USER_REMOVE_SEARCH table

USER_ID REM_USER_ID
1 2
1 4
2 1
2 3


now basically i want all records form USERS table except USER_ID 1 and except user ids in colum REM_USER_ID against user id 1

the query must produce this result because user id 1 is doing the search and, 2 and 4 exist in USER_REMOVE_SEARCH table against USER_ID 1

USER_ID USER_NAME
3 martin


I hope this makes it clear. If you are still not sure please let me know n i'll give it another try.


Thanks

 

[TonyGroves]

I think this should do the job:
[tt]
SELECT DISTINCT u.*
FROM
users u
LEFT JOIN user_remove_search urs
ON u.user_id=urs.rem_user_id
WHERE NOT (u.user_id=1 OR urs.user_id=1)
[/tt]

 

[zeeshan12]

I think i didnt explain it properly again

well lets just take it this way...this query brings all users in colums REM_USER_ID against USER_ID 1

select distinct u.*
from users u, user_remove_search urs
where
urs.user_id=1 and u.user_id=urs.rem_user_id


Now if i make this change

where
urs.user_id=1 and u.user_id<>urs.rem_user_id


this should bring all the users from excluding 2 and 4 because they existing in USER_REMOVE_SEARCH table against USER_ID 1. but for some reason its not working. it being back all users.

 

[TonyGroves]

The last solution I gave works for your last sample data. I'm afraid I don't see what the problem is.

 

[zeeshan12]

i didnt try the previous query but it didnt bring any results back.

and also looking at the query it says bring all user from users tabel where u.user = urs.rem_user_id. I actually want it to bring all users from users table where u.user_id is not equal to urs.rem_user_id.

 

[r937]

I actually want it to bring all users from users table where u.user_id is not equal to urs.rem_user_id.

getting more confuseder by the minute

r937.com | rudy.ca

 

[TonyGroves]

Maybe if you could repeat your problem in more detail, with a bigger sample data set (with expected results), it would make things clearer.

 

[zeeshan12]

Hey guyz,

i'm gonna try and explain the previous post again with more details

okay lets say USERS table have got these entries in it

USER_ID USER_NAME
1 zeeshan
2 jack
3 martin
4 kathrine
5 John
6 George
7 Leeane


Now USER_ID 1 searches the user table and all records get displayed but for the next time he conducts a search he doesnt want Jack (id=2) and Kathrine (id=4) to get displayed so he deletes them from his search and the system puts them in USER_REMOVE_SEARCH table against user_id 1. Now data in USER_REMOVE_SEARCH looks like this

USER_ID REM_USER_ID
1 2
1 4


Now when he searches again he should get all users except user_id 2 and user_id 4. Thats what i require a query for

The query must produce this result

USER_ID USER_NAME
1 zeeshan
3 martin
5 John
6 George
7 Leeane


I hope this makes it clear.


btw..thanks Tony i appriciate all your help so far.

 

[r937]

SELECT u.USER_ID
, u.USER_NAME
FROM users u
LEFT
JOIN user_remove_search urs
ON urs.user_id = u.user_id
and urs.user_id = 1
WHERE urs.user_id IS NULL


r937.com | rudy.ca

 

[TonyGroves]

A little correction to Rudy's query should get it working:
[tt]
SELECT u.*
FROM
users u
LEFT JOIN user_remove_search urs
ON urs.rem_user_id=u.user_id AND urs.user_id=1
WHERE urs.user_id IS NULL
[/tt]

 

[zeeshan12]

IT WORKED!!!!

you guyz've just put a smile on my face after 4 days is misery...

Thanks Tony, you are a star mate!!

 

[zeeshan12]

i dont wana be pain but would you mind explaining why this query doesn't work and yours do. becuase this is meant to produce similar results..

select distinct u.*
from users u, user_remove_search urs
where
urs.user_id=1 and u.user_id<>urs.rem_user_id

 

[r937]

it doesn't work because it's using an inner join

you clearly want a left outer join for this problem

thanks for the "little correction" tony

r937.com | rudy.ca