May 28, 2012 at 10:44 pm
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
May 28, 2012 at 11:00 pm
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.
May 28, 2012 at 11:04 pm
Yes you are correct. I edited the original post.
May 28, 2012 at 11:25 pm
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 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
May 29, 2012 at 12:18 am
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') )
May 29, 2012 at 12:28 am
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 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
May 29, 2012 at 12:39 am
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.
May 29, 2012 at 1:01 am
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