Applying join

  • I have two tables say A and B.

    Suppose A table has 19 columns and B table has 15 columns.

    I want to perform join on the table and i know that the column name are same on which the join has to be applied but unfortunately i don't know the column name .

    It would be a tedious job on my par to go and see each column name in table A with that of table B and then match them.

    I would like to have a query which would perform the above mention task for me

    e.g. A(a,b,.......k.........,s) //a,b,k,s etc are all column name

    B(q,w................k....r) //q,w,k,r etc are all column name.

    I want a query which would return me 'k'(as it is common in both the table)

  • SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table A'

    INTERSECT

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table B';

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (8/2/2012)


    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table A'

    INTERSECT

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table B';

    You can also use a INNER JOIN instead of INTERSECT to accomplish the same thing. Adds a bit more bulk to the query though.

    SELECT DISTINCT x.COLUMN_NAME

    FROM

    (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table A')x

    INNER JOIN (SELECT COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table B'

    )y ON x.COLUMN_NAME = y.COLUMN_NAME

  • Kirby1367 (8/2/2012)


    Koen Verbeeck (8/2/2012)


    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table A'

    INTERSECT

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table B';

    You can also use a INNER JOIN instead of INTERSECT to accomplish the same thing. Adds a bit more bulk to the query though.

    SELECT DISTINCT x.COLUMN_NAME

    FROM

    (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table A')x

    INNER JOIN (SELECT COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = 'Table B'

    )y ON x.COLUMN_NAME = y.COLUMN_NAME

    Both Good solutions but don't take the following into consideration:

    1) What if there are multiple schemas?

    2) What if there is more than one matching column?

    This query will address multiple schema's and will pick the first matching column

    DECLARE @table1 varchar(100) = 'orig_tblSite',

    @table2 varchar(100) = 'orig_tblCustVend'

    ;WITH y AS

    (

    SELECT COLUMN_NAME,COUNT(COLUMN_NAME) [ttl]

    FROM

    (

    SELECTTABLE_SCHEMA,TABLE_NAME,COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    ) x

    GROUP BY COLUMN_NAME

    HAVING COUNT(COLUMN_NAME)>1

    ),

    z AS

    (

    SELECT isc.TABLE_NAME,isc.COLUMN_NAME,isc.ORDINAL_POSITION

    FROM INFORMATION_SCHEMA.COLUMNS isc

    JOIN yON isc.COLUMN_NAME=y.COLUMN_NAME

    AND isc.TABLE_NAME IN (@table1,@table2)

    )

    SELECT TOP 1 COLUMN_NAME

    FROM z

    ORDER BY ORDINAL_POSITION

    GO

    And, if you replace

    SELECT TOP 1 COLUMN_NAME

    FROM z

    ORDER BY ORDINAL_POSITION

    GO

    With this:

    SELECT * FROM z

    ORDER BY COLUMN_NAME,TABLE_NAME

    You will get this:

    TABLE_NAME COLUMN_NAME ORDINAL_POSITION

    ----------------------- ---------------------------------

    orig_tblCustVend Address 4

    orig_tblSite Address 6

    orig_tblCustVend Address 2 5

    orig_tblSite Address 2 7

    orig_tblCustVend City 6

    orig_tblSite City 8

    orig_tblCustVend County 8

    orig_tblSite County 12

    orig_tblCustVend Name 2

    orig_tblSite Name 3

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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