Post reply

LEFT OUTER JOIN shows a value

  • January 5, 2006 at 10:04 am

    #91161

    I am having a problem while doing a "LEFT OUTER  JOIN", where I am getting a value (say "Y") even when there is no record in the table.

    Example

    (1) Please look at the query

    (2) Although there is no record in "table2" for empno = 100, still I am getting a value ("Y") for "some_value" column for this  (I was kind of expecting NULL)

    Any ideas why it's showing a value here?

    SELECT table1.empno,

               table2.some_value

    FROM  table1

    LEFT OUTER JOIN table2  ON table1.empno  = table2.empno

    WHERE table1.empno = 100

    Thanks

  • January 5, 2006 at 10:55 am

    #612965

    One more clarification in the above example both "table1 and table2" are views, if that makes any difference.(sorry I should have named them differently in the previous example intead of table I should have called probably view1 and view2)

  • January 5, 2006 at 12:26 pm

    #613016

    Sometimes it can happen that views are mapping the wrong fields. Especially if the basetables were changed.

    Is it possible to alter the view slightly so it is refreshed?

  • January 6, 2006 at 9:02 am

    #613278

    Check this article from Steve Jones: http://qa.sqlservercentral.com/columnists/sjones/outerjointrouble.asp

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply