Inner Join

  • How do I inner join between 2 tables(inner join TableA.ColA=TableB.ColA) with this kind of data ?

    My comparision values from TableA will be values after the '-' i.e (SS,SE,SD,SA)

    TableA TableB

    ColA ColBColA ColB

    ABCD-SS 2342 SS574

    DD-SE 52555SE7748568

    GHJKL-SD 6346SS7568

    U-SA 747SD8678

    Thanks

  • sqlnewbie17 (6/6/2016)


    How do I inner join between 2 tables(inner join TableA.ColA=TableB.ColA) with this kind of data ?

    My comparision values from TableA will be values after the '-' i.e (SS,SE,SD,SA)

    TableA TableB

    ColA ColBColA ColB

    ABCD-SS 2342 SS574

    DD-SE 52555SE7748568

    GHJKL-SD 6346SS7568

    U-SA 747SD8678

    Thanks

    You have a couple of choices, but most of them are going to perform horribly, because of the bad design. TableA.ColA should really be split into two columns.

  • Use LIKE: TableA.ColA LIKE '%' + TableB.ColA
  • Use Right (if it's always the same number of characters: RIGHT(TableA.ColA, 2) = TableB.ColA
  • Use SUBSTRING/CHARINDEX (if the number of characters after the '-' is variable and there is only one '-' in the string: SUBSTRING(TableA.ColA, CHARINDEX('-', TableA.ColA), LEN(ColA)) = TableB.ColA
  • Use RIGHT/CHARINDEX/REVERSE if the number of characters after the '-' is variable and there are more than one '-' in the string: SELECT CHARINDEX('-', 'ABC-DS'), RIGHT(TableA.ColA, CHARINDEX('-', REVERSE(TableA.ColA)) - 1) = TableB.ColA
  • Create a computed column using one of the above methods and create an index on that computed column, plus any other appropriate columns.
  • Only the last option has any hope of giving you good performance.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Use SUBSTRING/CHARINDEX (if the number of characters after the '-' is variable and there is only one '-' in the string: SUBSTRING(TableA.ColA, CHARINDEX('-', TableA.ColA), LEN(ColA)) = TableB.ColA

    The above condition does not work.It returns nothing when I use it in a join.

    When I run that on only TableA.ColA

    it gives this error:

    The multi-part identifier "TableA.ColA" could not be bound.

  • I tried this code on one of the tables which has col values as fadsf-ff,fsdf-ffg,edf-d

    and this code should select values after the '-' in the string from colA

    SELECT SUBSTRING ([DB].[dbo].[Table1].[ColA],

    CHARINDEX('_', [DB].[dbo].[Table1].[ColA]) + 1,

    LEN([DB].[dbo].[Table1].[ColA]))

    Its throwing a multi-part identifier error:

    The multi-part identifier "[DB].[dbo].[Table1].[ColA]" could not be bound

  • Specify an alias for Table1 in your FROM clause and use that in your SELECT list.

    John

  • sqlnewbie17 (6/8/2016)


    I tried this code on one of the tables which has col values as fadsf-ff,fsdf-ffg,edf-d

    and this code should select values after the '-' in the string from colA

    SELECT SUBSTRING ([DB].[dbo].[Table1].[ColA],

    CHARINDEX('_', [DB].[dbo].[Table1].[ColA]) + 1,

    LEN([DB].[dbo].[Table1].[ColA]))

    Its throwing a multi-part identifier error:

    The multi-part identifier "[DB].[dbo].[Table1].[ColA]" could not be bound

    Have you specified an alias for the table? If so, then you should use the alias.

    Is your database named DB?

    Is your table named Table1?

    Is your column named ColA?

    Also, your code is using an underscore '_', but the string is showing a hyphen '-'. You need to search for the hyphen, not the underscore.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Viewing 6 posts - 1 through 5 (of 5 total)

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