Left Join and Null values 1

[ingernet]

Hello,

I'm finally losing what's left of my mind, all thanks to what should be simple query (which is probably breaking all the rules of MySQL).

Here's the scoop: got a simple left join query:

select users.uId, user_activity.ua_uId 
  from users left join user_activity
  on users.uId = user_activity.ua_uId 
  where user_activity.ua_uId = NULL;

Essentially, I'm looking for all the users who DIDN'T have anything in the user_activity table.

I thought about doing something with a separate query to get everyone who *did* have an activity recorded, and then run a "not in" command against that, but that just seems messy.

Any ideas?

Thanks!
Inger

 

[r937]

what's the question?

your left join for all the users who DIDN'T have anything in the user_activity table looks okay -- did you run it? what did it produce?

the other query you mentioned is messy


rudy

 

[swampBoogie]

select users.uId, user_activity.ua_uId
  from users left join user_activity
  on users.uId = user_activity.ua_uId
  where user_activity.ua_uId IS NULL

Nothing is equal or unequal to null.

 

[r937]

ow

don't i look dumb now, eh

thanks, swampboogie

 

[ingernet]

rudy,

i got nothing. 0 records.

i suspect that it's because the NULL values are only created as SQL slaps together the results (there aren't any entries in the user_activity table where any of the columns *would* be NULL.

when i do this query -

select users.uId, user_activity.ua_uId
  from users left join user_activity
  on users.uId = user_activity.ua_uId

- here are my results:

uId	ua_uId
1	1
3	3
3	3
5	NULL
6	6
6	6
7	NULL

is it possible that the search for null values doesn't work because NULL values didn't exist until they were created ad hoc by SQL as it compiled a left join result? this seems the most logical reason. but i could have sworn that i'd seen other threads where people had done this. (of course, i can't find them NOW....)

thanks,
inger

 

[ingernet]

swampBoogie,

thanks for the reminder! that was it.

sigh...semantics, semantics, semantics.

thanks to both of you for your help.

inger