Query question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query question

Author

Topic

mica
Starting Member

5 Posts

Posted - 2007-10-10 : 14:04:30

Should be a pretty basic question:

I have two tables: people and telecommunication. telecommunication contains all the phone numbers for records in the people table. I need a query that will return all of the records in the people table but will also return a phone number from the telecommunications table only if it has a code of 'mobile'. So there may be p1, p2, p3, p4, p5 and only p4 has a mobile number, I need the result set to show:
person phone
p1
p2
p3
p4 m1
p5

Thanks,

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 14:12:39

Need DDL for the table, some sample rows, and expected result please.

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20to%20ask%20a%20question%20on%20SQL%20Team%20and%20get%20a%20quick%20answer

Kristen

mica
Starting Member

5 Posts

Posted - 2007-10-10 : 14:40:22

Like this?

Table People (p)
people_id....last_name
101..........Smith
102..........Jones
103..........Williams
104..........Jefferson
105..........Mills

Table Telecommunications (t)
Tele_id....people_id....tele_type....tele_number
1...............101.........home........123-456-7894
2...............102.........office......123-548-8547
3...............103.........office1.....144-545-8571
4...............104.........mobile......124-251-4124
5...............105.........fax.........124-854-8574

Expected results:
p.people_id....p.last_name....t.tele_typet.....tele_number
101..............Smith
102..............Jones
103..............Williams
104..............Jefferson..........mobile............124-251-4124
105..............Mills

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 15:21:16

Well, sort of. But from that starting point I will have to create a table, write the Insert statements, and then I can start writing the query. Right now I don't have time to do all that, sorry.

Kristen

mica
Starting Member

5 Posts

Posted - 2007-10-10 : 15:49:59

I see. Sorry for the confusion:

create table people
(people_id int,
last_name varchar(10))
insert into people (people_id, last_name)
select 101, 'smith'union all
select 102, 'jones'union all
select 103, 'williams'union all
select 104, 'jefferson'union all
select 105, 'mills'

create table telecom
(tele_id int,
people_id int,
tele_type varchar(10),
tele_number int)
insert into telecom (tele_id, people_id, tele_type, tele_number)
select 1,101,'home',1234567894 union all
select 2,102,'office',1235488547 union all
select 3,103,'office',1445458571 union all
select 4,104,'mobile',1242514124 union all
select 5,105,'fax',1248548574

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-10 : 16:01:02

This should do it

DECLARE @people table
(people_id int,
last_name varchar(10))
insert into @people (people_id, last_name)
select 101, 'smith'union all
select 102, 'jones'union all
select 103, 'williams'union all
select 104, 'jefferson'union all
select 105, 'mills'

declare @telecom table
(tele_id int,
people_id int,
tele_type varchar(10),
tele_number int)
insert into @telecom (tele_id, people_id, tele_type, tele_number)
select 1,101,'home',1234567894 union all
select 2,102,'office',1235488547 union all
select 3,103,'office',1445458571 union all
select 4,104,'mobile',1242514124 union all
select 5,105,'fax',1248548574

select p.people_id
,p.last_name
,'tele_type' = CASE WHEN t.tele_type = 'mobile' THEN 'mobile' ELSE null END
,'tele_number' = CASE WHEN t.tele_type = 'mobile' THEN t.tele_number ELSE null END
from
@people p
left join
@telecom t
on
p.people_id = t.people_id

Jim

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 16:22:12

You can move the CASE inot the OUTER JOIN. May make some small difference to Perfomance, I suppose ...


SELECT	P.people_id,
	P.last_name,
	T.tele_type,
	T.tele_number
FROM	@people AS P
	LEFT JOIN @telecom AS T
		 ON T.people_id = P.people_id
		AND T.tele_type = 'mobile'

Kristen

mica
Starting Member

5 Posts

Posted - 2007-10-10 : 16:36:15

Thanks, Jim. What about this scenario where people_id 104 has two telephone numbers and I only want to return the one with mobile.

DECLARE @people table
(people_id int,
last_name varchar(10))
insert into @people (people_id, last_name)
select 101, 'smith'union all
select 102, 'jones'union all
select 103, 'williams'union all
select 104, 'jefferson'union all
select 105, 'mills'

declare @telecom table
(tele_id int,
people_id int,
tele_type varchar(10),
tele_number int)
insert into @telecom (tele_id, people_id, tele_type, tele_number)
select 1,101,'home',1234567894 union all
select 2,102,'office',1235488547 union all
select 3,103,'office',1445458571 union all
select 4,104,'mobile',1242514124 union all
select 6,104,'office',1425474 union all
select 5,105,'fax',1248548574

select p.people_id
,p.last_name
,'tele_type' = CASE WHEN t.tele_type = 'mobile' THEN 'mobile' ELSE null END
,'tele_number' = CASE WHEN t.tele_type = 'mobile' THEN t.tele_number ELSE null END
from
@people p
left join
@telecom t
on
p.people_id = t.people_id

Expected results:
p.people_id....p.last_name....t.tele_typet.....tele_number
101..............Smith
102..............Jones
103..............Williams
104..............Jefferson..........mobile............124-251-4124
105..............Mills

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 16:42:52

"What about this scenario where people_id 104 has two telephone numbers and I only want to return the one with mobile"

I think my query works OK for that scenario.

Kristen

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-10 : 17:08:03

Probably should have done it this way anyway

select p.people_id
,p.last_name
,t.tele_type
,t.tele_number
from
@people p
left join
(select *
from @telecom
where tele_type = 'mobile'
)t
on
p.people_id = t.people_id

Jim

mica
Starting Member

5 Posts

Posted - 2007-10-10 : 17:21:35

Works great, Kristen.

Thanks for your patience, guys.

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 00:40:37

@jimf:

What does the sub-select get you?

Won't just joining to @telecom do the trick? (and be more readable