Post reply

Outer Join Problem

  • March 31, 2009 at 1:45 pm

    #129009

    Table 1

    GroupName GroupID GroupDesc

    Group1 1Item1

    Group1 2Item2

    Group1 3Item3

    Group1 4Item4

    Group1 5Item5

    Group1 6Item6

    Table 2

    ProfileID ProfileDate GroupID

    A 1/1/2009 1

    A 1/8/2009 2

    A 3/7/2009 3

    B 1/2/2009 4

    C 3/2/2009 2

    A 2/9/2009 6

    I want to join them to look like this for ProfileID = "A"

    GroupName GroupID GroupDesc ProfileID ProfileDate GroupID

    Group1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspItem1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspA&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp1/1/2009&nbsp&nbsp1

    Group1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspItem2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspA&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp1/8/2009&nbsp&nbsp2

    Group1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspItem3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspA&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp3/7/2009&nbsp&nbsp3

    Group1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp4&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspItem4

    Group1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp5&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspItem5

    Group1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp6&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspItem6&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspA&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp2/9/2009&nbsp&nbsp6

    However, my table looks like this (items 4 & 5 are missing)

    GroupName GroupID GroupDesc ProfileID ProfileDate GroupID

    Group1 1Item1 A 1/1/2009 1

    Group1 2Item2 A 1/8/2009 2

    Group1 3Item3 A 3/7/2009 3

    Group1 6Item6 A 2/9/2009 6

    My join statement is this:

    SELECT *

    FROM Table1 LEFT OUTER JOIN Table2

    ON Table1.GroupID = Table2.GroupID

    WHERE (Table2.ProfileID = 'A')

  • March 31, 2009 at 2:08 pm

    #969438

    Hi

    If your criterion shall be used for the join you have to put it there:

    SELECT *

    FROM Table1 LEFT OUTER JOIN Table2

    ON Table1.GroupID = Table2.GroupID Table2.ProfileID = 'A'

    Greets

    Flo

  • March 31, 2009 at 2:23 pm

    #969461

    Thanks for your reply!

    When I put in the code above, I get a syntax error.

  • March 31, 2009 at 2:27 pm

    #969463

    SELECT

    *

    FROM

    Table1

    LEFT OUTER JOIN Table2

    ON (Table1.GroupID = Table2.GroupID

    AND Table2.ProfileID = 'A');

  • March 31, 2009 at 2:32 pm

    #969466

    Dang! Sorry... I shouldn't try blind typing...

    Thanks for correction Lynn!

    Greets

    Flo

  • March 31, 2009 at 2:35 pm

    #969469

    No prob, Flo. Easily corrected error.

  • March 31, 2009 at 2:42 pm

    #969475

    AWESOME... Worked like a charm!

    Thanks guys!! :w00t:

Viewing 7 posts - 1 through 7 (of 7 total)

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