Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Question Unanswered: Unexpected behaviour for left join

    I've come across a somewhat unexpected behavior when using custom text in a select and join statement. I use Access 2010. This is a minimal working example

    TableA contains:
    Id
    1
    2
    3

    TableB contains:
    Id
    1

    Code:
    SELECT A.Id, S.CustomText FROM TableA AS A
    LEFT JOIN (
      SELECT Id, 'My text' as CustomText FROM TableB
    ) as S ON A.Id = S.Id
    Expected result:
    Id CustomText
    1 My text
    2
    3

    Actual result (bold is unexpected):
    Id CustomText
    1 My text
    2 My text
    3 My text

    What just happened?? The curious part is that both statements below works just fine..

    Code:
    SELECT A.Id, S.CustomText FROM TableA AS A
    LEFT JOIN (
      SELECT Id, Id as CustomText FROM TableB
    ) as S ON A.Id = S.Id
    Id CustomText
    1 1
    2
    3

    Code:
    SELECT A.Id, S.CustomText FROM TableA AS A
    LEFT JOIN (
      SELECT Id, 'My text' as CustomText FROM TableB UNION
      SELECT Id, 'My text' as CustomText FROM TableB
    ) as S ON A.Id = S.Id
    Id CustomText
    1 My text
    2
    3

    Ideas anyone?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Weird but good to know. Thanks for sharing!
    Have a nice day!

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I'm still stuck on Access 2k, but try adding "WHERE S.ID Is Null" and see what that gives you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

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