Post reply

Joining to a table based on Prefix value of Column

  • July 26, 2007 at 8:19 am

    #118006

    Hi all,

    I have an ID column (CompanyNumber) that I wish to to join to a table based on the prefix value of that column. For example, if the column starts with 'IE' I need to goto another table 'ForiegnCompany' to get additional information. How would I do this query? I have the following which I know i wrong.

    SELECT g.orgnr_daughter, g.name_daughter

    FROM groupe_structure g OUTER JOIN ForiegnCompany fc on (g.orgnr_daughter = fc.orgnr)

    So basically I need to print all companys 'number' and 'name' from the 'groupe_structure' table, but if a company number is prefixed with a 'IE' I need to get its name from ForiegnCompany table.

    Thanks in advance.

  • July 26, 2007 at 8:22 am

    #722586

    Hi Trystan,

    Someone posted a similar question last week.

    See if this post helps:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=383149#bm383204

    thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 26, 2007 at 9:29 am

    #722611

    Hi and thanks for the reply.

    I am not using a SP, and also, the join doesn't happen all the time, its only when a PREFIX is satisfied.

  • July 26, 2007 at 10:13 am

    #722632

    Do you mean this?

    SELECT

     g.orgnr_daughter

     ,name_daughter = CASE WHEN Left(g.orgnr_daughter,2) = 'IE' THEN fc.orgnr_daughter ELSE g.orgnr_daughter END

    FROM

     groupe_structure AS g

     

     LEFT OUTER JOIN ForeignCompany AS fc ON

      g.orgnr_daughter = fc.orgnr

    Completely untested, and I made an assumption about the column name in fc for the name, but it should do what I think you're asking for.

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

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