SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 06/20/2013 :  15:25:38  Show Profile  Reply with Quote
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


Edited by - funk.phenomena on 06/20/2013 15:31:15

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 06/20/2013 :  16:10:20  Show Profile  Reply with Quote
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

United Kingdom
34 Posts

Posted - 06/20/2013 :  17:53:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 06/20/2013 :  17:55:46  Show Profile  Reply with Quote
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

United Kingdom
34 Posts

Posted - 06/20/2013 :  18:00:03  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000