query - SQL Server Forums

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

query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-30 : 07:07:44

Rhys writes "well i am a novice at sql server 2005...
i have a doubt on how to create a query for
"List all of the people who have a cell phone number, an office number and a home number in the database."
the related tables are

People Telephone

PeopleID PhoneID
Name Category
Address

under the category comes "office","home" and "cell"
how to effectively write a query for this?
shall i use multiple join or nested and?"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 07:12:19

some sample data would be nice.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-30 : 07:14:19

This thread does not have title at all ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 07:20:14

Something like this maybe?

SELECT		p.*
FROM		People p
INNER JOIN	(
			SELECT		PhoneID,
					SUM(CASE WHEN Category = 'Office' THEN 1 ELSE 0 END) Office,
					SUM(CASE WHEN Category = 'Home' THEN 1 ELSE 0 END) Home,
					SUM(CASE WHEN Category = 'Cell' THEN 1 ELSE 0 END) Cell,
			FROM		Telephone
			GROUP BY	PhoneID
		) d on d.PhoneID = p.PeopleID
WHERE		SIGN(Office) + SIGN(Home) + SIGN(Cell) = 3

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 07:34:19

quote:
Originally posted by khtan

This thread does not have title at all ?

I found the space

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources