SQL Server Performance

Problem Regarding Inner Join

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Jun 10, 2010.

  1. shankbond New Member

    Hi,
    I am having a table( a Junction Table aka Cross Reference table ) named XrefList,
    This table is holding TableIds Which are the primary keys of (Table1 & Table2) and also a Pointer Type bit (isTable1Data)
    The Problem Is : one table is having an INT Id Where as other table is having a VARCHAR(x) ID, The INNER JOIN Works Fine for table2 having a VARCHAR(x) ID where as
    table1 has a problem that it will only return the first matching record only.


    CREATE TABLE XrefList(
    ListId INT NOT NULL,
    Columna VARCHAR(50) NOT NULL,
    IsTable1Data BIT
    );
    GO
    --Loading Table
    INSERT INTO XrefList (ListId, Columna, IsTable1Data)
    SELECT 123, 'CD123', 'False'
    UNION ALL
    SELECT 123, '123', 'True'
    UNION ALL
    SELECT 123, 'CD121', 'False'
    UNION ALL
    SELECT 123, '52', 'True'
    UNION ALL
    SELECT 123, 'CD120', 'False'
    GO
    CREATE TABLE Table1(
    TableId INT NOT NULL,
    Columna VARCHAR(50) NOT NULL
    );
    GO
    INSERT INTO Table1 (TableId, Columna)
    SELECT 1, 't1'
    UNION ALL
    SELECT 2, 't2'
    UNION ALL
    SELECT 123, 't123'
    UNION ALL
    SELECT 152, 't152'
    UNION ALL
    SELECT 21, 't21'
    GO
    CREATE TABLE Table2(
    TableId VARCHAR(50) NOT NULL,
    Columna VARCHAR(50) NOT NULL
    );
    GO
    INSERT INTO Table2 (TableId, Columna)
    SELECT 'CD1', 'h1'
    UNION ALL
    SELECT 'CD120', 'h2'
    UNION ALL
    SELECT 'CD123', 'h123'
    UNION ALL
    SELECT 'CD152', 'h152'
    UNION ALL
    SELECT 'CD121', 'h21'
    GO

    The Problem Equation
    SELECT t1.Columna FROM Table1 t1 INNER JOIN
    XrefList x ON t1.TableId=x.Columna WHERE x.IsTable1Data='True'
    Is there any Resolution to this.
  2. patel_mayur New Member

    Don't you think the join columns are incorrect. It should beSELECT
    t1.Columna FROM Table1 t1 INNER JOINXrefList x
    ON t1.TableId=x.listid WHERE x.IsTable1Data='True'
    Which returns correct result set.
  3. shankbond New Member

    Hi Mayur,
    [quote user="patel_mayur"]Don't you think the join columns are incorrect.[/quote]
    The joined columns are correct.
    Is there a solution?
    Please Reply

Share This Page