Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    53

    Unanswered: Am I misunderstanding the outer join?

    As I understand it, a left outer join should include records from the left table which match the where clauses for that table whether or not there are records from the right table that match the where clauses for that table.

    CREATE TABLE dbo.Test1 (
    testnumber varchar (10) NOT NULL
    ) ON PRIMARY
    GO

    CREATE TABLE dbo.Testlink (
    testnumber varchar (10) NOT NULL ,
    testdate datetime NOT NULL
    ) ON PRIMARY
    GO

    INSERT into dbo.Test1 values ('0051419')
    GO

    INSERT into dbo.Testlink values ('0051419','2004-11-22')
    GO
    INSERT into dbo.Testlink values ('0051419','2004-11-29')
    GO
    INSERT into dbo.Testlink values ('0051419','2004-12-22')
    GO

    select test1.testnumber, testlink.testdate
    from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
    where test1.testnumber='0051419'

    returns, as expected,

    testnumber testdate
    ---------- ------------------------------------------------------
    0051419 2004-11-22 00:00:00.000
    0051419 2004-11-29 00:00:00.000
    0051419 2004-12-22 00:00:00.000

    (3 row(s) affected)

    adding a where clause that results in no records matching in testlink however, results in no records being returned-but I expected the record from test1 being returned with a null value in testdate. Am I misunderstanding what should be happening? Or am I simply doing something wrong?

    select test1.testnumber, testlink.testdate
    from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
    where test1.testnumber='0051419'
    and testlink.testdate > '10/16/2006'

    testnumber testdate
    ---------- ------------------------------------------------------

    (0 row(s) affected)

    In the report I'm creating I do need to report the test1 records that meet the selection criteria even when there are no records in testlink that meet the selection criteria for that table.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - you are missing something. Everything you say is correct until you get to the bit about ALWAYS returning all rows from the left table that meet the where clause. You can think of the where clause being applied AFTER the join. You effectively want to apply the filter on testlink BEFORE the join. You can use a Derived Table or you can rely on a handy bit of syntax:
    Code:
    select test1.testnumber, testlink.testdate
    from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
    and testlink.testdate > '10/16/2006'
    where test1.testnumber='0051419'
    Putting the expression in the join syntax applies the filter before the left join is applied and should result in your required output.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2006
    Posts
    53
    Hmmm. Thanks-that answers my question. It doesn't quite solve my problem but that's because I didn't sufficiently outline the circumstances-and I'm not sure the remaining issue is right for this forum. The remaining problem is that I'm using Crystal Reports & don't see an option for comparing a field to a constant value in defining the link (join). I'll go out to the Crystal site & see if I can get an answer to that question there.

    Thanks a lot for explaining what I was missing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •