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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple Question

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 BANK
4165551234 Wachovia
4165551234 Scotiabank
4165551234 Chase

4165551239 Wachovia
4165551239 Chase

4165551240 BankOfAmerica
4165551240 Wachovia




I 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 BANK
4165551234 Wachovia
4165551234 Scotiabank
4165551234 Chase

4165551239 Wachovia
4165551239 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 T1
INNER JOIN
(
SELECT
TELNUM
FROM
ACCOUNT_TABLE
WHERE
BANK = 'Chase'
) AS T2
ON T1.TELNUM = T2.TELNUM
Go to Top of Page

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 T1
INNER 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.telnum

FROM T1

WHERE T1.bank = 'Chase'


Go to Top of Page

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.
Go to Top of Page

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 T1
INNER 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.telnum

FROM T1

WHERE T1.bank = 'Chase'






Ignore me. I re-read the original query
Go to Top of Page
   

- Advertisement -