Joining on a non existant value 1

[travs69]

I have 3 tables (lets call them a,b,c) that each have a col called UID.
I have a basic query like
select a.test,c.test from a,b,c where a.uid = b.uid and a.uid=c.uid

Now this works just fine.. but now I have c.uid as sometimes not existing. I would like to still like to return a.test but of course this won't work with the above query. I could break it into to separate querys but what's the fun in that??

I was thinking that using exists would work.. but I couldn't seem to incorporate it without returning back every value of c.test.

Thanks in advance

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[TonyGroves]

You can use LEFT JOIN:
[tt]
SELECT a.test,c.test
FROM
a
JOIN b ON a.uid=b.uid
LEFT JOIN c ON b.uid=c.uid
[tt]
That query would return a record with a NULL value for c.test in the case where a matching c record does not exist.

 

[travs69]

Thanks.. I'll test that out!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[travs69]

hmm.. somehow I screwed that up. Lets uses some real table names and see if that gets me a little farther.
Real query that works if uid exists everywhere ($session_id is filled in by perl):

select users.name,role.name 
from 
users,sessions,users_roles,role 
where 
users.uid = sessions.uid and users.uid = users_roles.uid and sessions.uid != 0 and ((NOW() - FROM_UNIXTIME(sessions.timestamp))/3600) < 12 and sessions.sid =$session_id

if the user doesn't have a role assigned to him his uid will not show up in users_roles. In my current query if he doesn't have a role I get nothing back at all, but I still need to return users.name if everything else is true (user.uid = sessions.uid, sessions.uid != 0, sessions.sid = $session_id, and the whole timestamp mess up )


Thanks again for the help.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[TonyGroves]

You would be better off rephrasing that query using JOIN syntax (instead of comma-join syntax), like in the code I posted; it would make things much clearer.

I notice also that your join with the "role" table is unconditional; surely that's not what you want? Again, using JOIN syntax would make that stick out like a sore thumb.

 

[travs69]

I was just showing you my old query for help on building the new. I should have pasted the new one also. I actually got it working by your example.. I had to read up a little on join to see where I was going wrong. Thanks again.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;