Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
funk.phenomena
Posting Yak Master
121 Posts |
Posted - 2013-06-20 : 15:25:38
|
Hi All - I have a table as such below (very very simplified version): [CODE]TELNUM BANK4165551234 Wachovia4165551234 Scotiabank4165551234 Chase4165551239 Wachovia4165551239 Chase4165551240 BankOfAmerica4165551240 WachoviaI need it to output all telephone numbers that have an instance CHASE on it. For example, I would like the above table outputted as such:TELNUM BANK4165551234 Wachovia4165551234 Scotiabank4165551234 Chase4165551239 Wachovia4165551239 Chase How can I achieve this? Thanks!SELECT TELNUM, BANK FROM ACCOUNT_TABLE T1[/code] |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-06-20 : 16:10:20
|
Here is one way:SELECT T1.TELNUM, T1.BANK FROM ACCOUNT_TABLE AS T1INNER JOIN ( SELECT TELNUM FROM ACCOUNT_TABLE WHERE BANK = 'Chase' ) AS T2 ON T1.TELNUM = T2.TELNUM |
|
|
nevzab
Starting Member
34 Posts |
Posted - 2013-06-20 : 17:53:00
|
quote: Originally posted by Lamprey Here is one way:SELECT T1.TELNUM, T1.BANK FROM ACCOUNT_TABLE AS T1INNER JOIN ( SELECT TELNUM FROM ACCOUNT_TABLE WHERE BANK = 'Chase' ) AS T2 ON T1.TELNUM = T2.TELNUM
Hi, I am new to SQL and wondered why you opted for this method rather than this simplified version. Am I missing something? SELECT T1.telnumFROM T1WHERE T1.bank = 'Chase' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-20 : 17:55:46
|
Nevzab, if you did that you would get only two rows. OP wants to get 5 rows - i.e., the goal is find all instances of Chase, then find the phone numbers associated with those instances of Chase, and retrieve all the rows that have those phone numbers, regardless of whether the Bank is Chase or not. |
|
|
nevzab
Starting Member
34 Posts |
Posted - 2013-06-20 : 18:00:03
|
quote: Originally posted by nevzab
quote: Originally posted by Lamprey Here is one way:SELECT T1.TELNUM, T1.BANK FROM ACCOUNT_TABLE AS T1INNER JOIN ( SELECT TELNUM FROM ACCOUNT_TABLE WHERE BANK = 'Chase' ) AS T2 ON T1.TELNUM = T2.TELNUM
Hi, I am new to SQL and wondered why you opted for this method rather than this simplified version. Am I missing something? SELECT T1.telnumFROM T1WHERE T1.bank = 'Chase'
Ignore me. I re-read the original query |
|
|
|
|
|
|
|