Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
youruseridistoxic
Yak Posting Veteran
65 Posts |
 Posted - 2009-03-31 : 08:12:07
|
| I'm running into an issue whereby records are being omitted from the resultset. I'm slowly but surely gaining a better understanding of the join types out there, but it seems no matter which I use - the resultset is the same.select tk.first+' '+tk.last as [CompleteName], tk.ID, userid.udvalue as [NetworkID], tk.email, telext.udvalue as [PrimaryPhone]from timer tkinner join udf useridon tk.ID = userid.udjoinleft outer join udf telexton tk.ID = telext.udjoinwhere userid.udfindex = '3'and telext.udfindex = '2'and tk.termdate is nullThe current resultset looks like this;Joe Smith 0014 jsmith jsmith@email.com 8514More specifically, it is omitting records where telext.udvalue = NULL, which I assume would look like this;Jane Smith 0022 jasmith jasmith@email.com NULLWhat I find truly strange is there ARE records in the resultset that look like this;Alison Jones 6965 NULL ajones@email.com NULLThe last example kind of makes me wonder if its not the join type at all...but rather the AND operator being used to limit the results to udfindex = 2 and 3.Given the expected result set, how can I include records from dbo.UDF where no record exists for UDFINDEX = 2, or UDFINDEX = 3 ??? |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-03-31 : 08:15:56
|
| It would be much easier to answer this question if we had some table definitions, sample data, and desired output. Follow the first link in my signature to see how to provide this.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
|
|
|
|
|
Join Problem