Use a second table to make selections from another table

  • I'm having trouble with this selection. I have the following two tables. I want to use a SYMBOL in Table 1 to select all the other symbols in Table 1 that have the same Sector (as found in Table2)

    So input 'AADR' would return 'AACC', 'AAIT' because they are "Financial"

    SELECT T1a.Symbol, T1b.Symbol, Table2.Sector FROM Table1 AS T1a, Table1 AS T1b, Table2

    INNER JOIN Table2 ON Table1.Symbol = Table2.Symbol

    INNER JOIN Table2 ON Table1.Symbol = Table2.Symbol

    WHERE Table1.Symbol = 'AADR';

    TABLE 1

    ID SYMBOL DATE PRICE VOLUME

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

    1A 05/24/12 123.45 100500

    2AA 05/24/12 56.35 4589520

    3AACC 05/24/12 81.60 321000

    4AADR 05/24/12 12.25 45080

    5AAIT 05/24/12 61.75 125700

    TABLE 2

    ID SYMBOL COMPANY SECTOR

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

    1AAgilent Technologies Inc. Technology

    2AACAustralia Acquisition Corp. Conglomerates

    3AACCAsset Acceptance Capital Corp. Financial

    4AADRWCM/BNY Mellon Focused Growth ADR ETF Financial

    5AAITiShares MSCI AC Asia Information Tech Financial

    6BBYBest Buy Co. Inc. Services

  • Maybe I am misreading your table, but shouldn't 'AADR' return 'AADR', 'AAIT', and 'AACC'? It looks like 'AACC' also belongs to the Financial Sector.

  • Yes you are correct. I edited the original post.

  • You can try something like this:

    DECLARE @TABLE1 TABLE

    (ID INT, SYMBOL VARCHAR(4), DATE DATETIME, PRICE MONEY, VOLUME INT)

    DECLARE @TABLE2 TABLE

    (ID INT, SYMBOL VARCHAR(4), COMPANY VARCHAR(100), SECTOR VARCHAR(50))

    INSERT INTO @TABLE1

    SELECT 1,'A','05/24/12',123.45,100500

    UNION ALL SELECT 2,'AA','05/24/12',56.35, 4589520

    UNION ALL SELECT 3,'AACC','05/24/12',81.60, 321000

    UNION ALL SELECT 4,'AADR','05/24/12',12.25, 45080

    UNION ALL SELECT 5,'AAIT','05/24/12',61.75, 125700

    INSERT INTO @TABLE2

    SELECT 1,'A','Agilent Technologies Inc.','Technology'

    UNION ALL SELECT 2,'AAC','Australia Acquisition Corp.','Conglomerates'

    UNION ALL SELECT 3,'AACC','Asset Acceptance Capital Corp.','Financial'

    UNION ALL SELECT 4,'AADR','WCM/BNY Mellon Focused Growth ADR ETF','Financial'

    UNION ALL SELECT 5,'AAIT','iShares MSCI AC Asia Information Tech','Financial'

    UNION ALL SELECT 6,'BBY','Best Buy Co. Inc.','Services'

    SELECT t3.SYMBOL, t3.COMPANY

    FROM @TABLE1 t1

    INNER JOIN @TABLE2 t2 ON t1.SYMBOL = t2.SYMBOL

    CROSS APPLY (SELECT SYMBOL, COMPANY FROM @TABLE2 t3 WHERE t2.SECTOR = t3.SECTOR) t3

    WHERE t1.SYMBOL = 'AADR'


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • You can also do it using simple sub queries:

    --Creating Tables

    Create Table Table1

    (ID int,

    SYMBOL varchar(10),

    DATE Date, PRICE Float,

    VOLUME int )

    Create Table Table2

    (ID int,

    SYMBOL varchar(10),

    COMPANY varchar(50),

    SECTOR varchar(30) )

    --Inserting Sample Data

    Insert Into Table1

    Select 1, 'A', '05/24/12', 123.45, 100500

    Union all

    Select 2, 'AA', '05/24/12', 56.35, 4589520

    Union all

    Select 3, 'AACC', '05/24/12', 81.60, 321000

    Union all

    Select 4, 'AADR', '05/24/12', 12.25, 45080

    Union all

    Select 5, 'AAIT', '05/24/12', 61.75, 125700

    Insert Into Table2

    Select 1, 'A', 'Agilent Technologies Inc.', 'Technology'

    Union ALL

    Select 2, 'AAC', 'Australia Acquisition Corp.', 'Conglomerates'

    Union ALL

    Select 3, 'AACC', 'Asset Acceptance Capital Corp.', 'Financial'

    Union ALL

    Select 4, 'AADR', 'WCM/BNY Mellon Focused Growth ADR ETF', 'Financial'

    Union ALL

    Select 5, 'AAIT', 'iShares MSCI AC Asia Information Tech', 'Financial'

    Union ALL

    Select 6, 'BBY', 'Best Buy Co. Inc.', 'Services'

    --Query For Your Requirement

    Select SYMBOL, COMPANY From Table2

    Where SECTOR

    IN

    (Select SECTOR From Table2 Where SYMBOL

    IN

    (Select SYMBOL From Table1 Where SYMBOL = 'AADR') )

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (5/29/2012)


    You can also do it using simple sub queries:

    --Creating Tables

    Create Table Table1

    (ID int,

    SYMBOL varchar(10),

    DATE Date, PRICE Float,

    VOLUME int )

    Create Table Table2

    (ID int,

    SYMBOL varchar(10),

    COMPANY varchar(50),

    SECTOR varchar(30) )

    --Inserting Sample Data

    Insert Into Table1

    Select 1, 'A', '05/24/12', 123.45, 100500

    Union all

    Select 2, 'AA', '05/24/12', 56.35, 4589520

    Union all

    Select 3, 'AACC', '05/24/12', 81.60, 321000

    Union all

    Select 4, 'AADR', '05/24/12', 12.25, 45080

    Union all

    Select 5, 'AAIT', '05/24/12', 61.75, 125700

    Insert Into Table2

    Select 1, 'A', 'Agilent Technologies Inc.', 'Technology'

    Union ALL

    Select 2, 'AAC', 'Australia Acquisition Corp.', 'Conglomerates'

    Union ALL

    Select 3, 'AACC', 'Asset Acceptance Capital Corp.', 'Financial'

    Union ALL

    Select 4, 'AADR', 'WCM/BNY Mellon Focused Growth ADR ETF', 'Financial'

    Union ALL

    Select 5, 'AAIT', 'iShares MSCI AC Asia Information Tech', 'Financial'

    Union ALL

    Select 6, 'BBY', 'Best Buy Co. Inc.', 'Services'

    --Query For Your Requirement

    Select SYMBOL, COMPANY From Table2

    Where SECTOR

    IN

    (Select SECTOR From Table2 Where SYMBOL

    IN

    (Select SYMBOL From Table1 Where SYMBOL = 'AADR') )

    Try it with this test harness:

    DECLARE @TABLE1 TABLE

    (ID INT, SYMBOL VARCHAR(11), DATE DATETIME, PRICE MONEY, VOLUME INT)

    DECLARE @TABLE2 TABLE

    (ID INT, SYMBOL VARCHAR(11), COMPANY VARCHAR(100), SECTOR VARCHAR(50))

    INSERT INTO @TABLE1

    SELECT 1,'A','05/24/12',123.45,100500

    UNION ALL SELECT 2,'AA','05/24/12',56.35, 4589520

    UNION ALL SELECT 3,'AACC','05/24/12',81.60, 321000

    UNION ALL SELECT 4,'AADR','05/24/12',12.25, 45080

    UNION ALL SELECT 5,'AAIT','05/24/12',61.75, 125700

    INSERT INTO @TABLE2

    SELECT 1,'A','Agilent Technologies Inc.','Technology'

    UNION ALL SELECT 2,'AAC','Australia Acquisition Corp.','Conglomerates'

    UNION ALL SELECT 3,'AACC','Asset Acceptance Capital Corp.','Financial'

    UNION ALL SELECT 4,'AADR','WCM/BNY Mellon Focused Growth ADR ETF','Financial'

    UNION ALL SELECT 5,'AAIT','iShares MSCI AC Asia Information Tech','Financial'

    UNION ALL SELECT 6,'BBY','Best Buy Co. Inc.','Services'

    ;WITH Tally (n) AS (

    SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2)

    INSERT INTO @TABLE1

    SELECT (ID * 1000) + n

    ,SYMBOL + RIGHT('0000000' + CAST(n AS VARCHAR(7)), 7)

    ,DATE,PRICE,VOLUME

    FROM @TABLE1 CROSS APPLY Tally

    ;WITH Tally (n) AS (

    SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2)

    INSERT INTO @TABLE2

    SELECT (ID * 1000) + n

    ,SYMBOL + RIGHT('0000000' + CAST(n AS VARCHAR(7)), 7)

    ,COMPANY, SECTOR

    FROM @TABLE2 CROSS APPLY Tally

    SET STATISTICS TIME ON

    SET STATISTICS IO ON

    SELECT t3.SYMBOL, t3.COMPANY, t3.SECTOR

    FROM @TABLE1 t1

    INNER JOIN @TABLE2 t2 ON t1.SYMBOL = t2.SYMBOL

    CROSS APPLY (SELECT SYMBOL, COMPANY, SECTOR FROM @TABLE2 t3 WHERE t2.SECTOR = t3.SECTOR) t3

    WHERE t1.SYMBOL = 'AADR'

    Select SYMBOL, COMPANY From @Table2

    Where SECTOR

    IN

    (Select SECTOR From @Table2 Where SYMBOL

    IN

    (Select SYMBOL From @Table1 Where SYMBOL = 'AADR') )

    SET STATISTICS TIME OFF

    SET STATISTICS IO OFF

    Results I see are:

    (3003 row(s) affected)

    Table '#6754599E'. Scan count 1, logical reads 90090, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#68487DD7'. Scan count 2, logical reads 104, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 1606 ms, elapsed time = 1639 ms.

    (3003 row(s) affected)

    Table '#6754599E'. Scan count 1, logical reads 3003, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table '#68487DD7'. Scan count 2, logical reads 159211, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 2106 ms, elapsed time = 2138 ms.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Yes, on a larger set of data your method works better.

    I had checked the stats on the same data set that the OP supplied....on that set the stats for both our queries was the same.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • dwain.c (5/28/2012)


    You can try something like this:

    SELECT t3.SYMBOL, t3.COMPANY

    FROM @TABLE1 t1

    INNER JOIN @TABLE2 t2 ON t1.SYMBOL = t2.SYMBOL

    CROSS APPLY (SELECT SYMBOL, COMPANY FROM @TABLE2 t3 WHERE t2.SECTOR = t3.SECTOR) t3

    WHERE t1.SYMBOL = 'AADR'

    dwain.c,

    Thanks, your method works. I had some difficulty applying to my actual case but it works as I needed.

    Again thanks.

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

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