Help with join 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

SQL Server 2012 Forums

Transact-SQL (2012)

Help with join query

Author

Topic

littlewing
Starting Member

33 Posts

Posted - 2013-06-07 : 08:43:09

I have the following 4 tables:

PhoneType (id, name)
1 Work
2 Home
3 Cell
4 Fax

Phone (id, phonenumber, phonetypeid)
1 555-1234 1
2 555-3456 2
3 555-8765 1
4 555-7654 2
5 555-2222 3
6 555-3232 1
7 555-2121 2
8 555-4444 3
9 555-5454 4
10 555-9543 4

UserPhone(userid, phoneid)
1 1
1 2
2 3
2 4
2 5
3 6
3 7
3 8
3 9
4 10

User(userid, firstname, lastname)
1 John Smith
2 Sue Brown
3 Rob Green
4 Dave Black

A User can have 1 or more phone numbers (UserPhone table). I need to query the tables so that for each user, 4 phone numbers are returned, with the appropriate number if present or empty if not.
My query needs to return it's results thus:

Userid Firstname Lastname WorkPhone HomePhone Cell Fax
1 John Smith 555-1234 555-3456
2 Sue Brown 555-8765 555-7654 555-2222
3 Rob Green 555-3232 555-2121 555-4444 555-5454
4 Dave Black 555-9543

My initial thought would be to create a temp table, run a cursor through the User table, and pull the numbers using an If Exists type of call. Is that the right approach?
I know cursors are generally frowned upon, if the SQL masters have a better solution it's much appreciated.

Following works, it just seems so wrong...

DECLARE @t_result TABLE (UserID int, HomePhone varchar(50), WorkPhone varchar(50), Cell varchar(50), Fax varchar(50))--;

DECLARE cur CURSOR FOR
SELECT UserId
FROM User

OPEN cur;
FETCH NEXT FROM cur INTO @userid;

WHILE @@FETCH_STATUS = 0
BEGIN

insert into @t_result
values (
@userid,
(select p.phonenumber from phone p
inner join Userphone np ON np.phoneid = p.id
inner join User n on n.userid = np.userid
where np.userid = @userid
and p.phoneTypeId = 1),
ISNull((select p.phonenumber from phone p
inner join Userphone np ON np.phoneid = p.id
inner join User n on n.userid = np.userid
where np.userid = @userid
and p.phoneTypeId = 2), ''),
ISNull((select p.phonenumber from phone p
inner join Userphone np ON np.phoneid = p.id
inner join User n on n.userid = np.userid
where np.userid = @userid
and p.phoneTypeId = 3), ''),
ISNull((select p.phonenumber from phone p
inner join Userphone np ON np.phoneid = p.id
inner join User n on n.userid = np.userid
where np.userid = @userid
and p.phoneTypeId = 4), '')
)

FETCH NEXT FROM cur INTO @userid;

END
CLOSE cur;
DEALLOCATE cur;

select * from @t_result

Thanks,
LW

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 10:44:20

No need of cursor. Think set based
All you need is this


SELECT userid,firstname,lastname,[Work] AS WorkPhone,[Home] AS HomePhone,[Cell],[Fax]
FROM
(
SELECT u.userid,u.firstname,u.lastname,p.phonenumber,pt.name
FROM User u
INNER JOIN UserPhone up
ON up.Userid = u.userid
INNER JOIN phone p
ON p.id = up.phoneid
INNER JOIN phonetype pt
ON pt.id = p.phonetypeid
)t
PIVOT(MAX(phonenumber) FOR name IN ([Work],[Home],[Cell],[Fax]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-07 : 10:53:31

You can simplify your query by using PIVOT function
[CODE]

DECLARE @PhoneType TABLE (id INT, name VARCHAR(10))
INSERT INTO @PhoneType VALUES
(1, 'Work'),
(2, 'Home'),
(3, 'Cell'),
(4, 'Fax');

DECLARE @Phone TABLE (id INT, phonenumber VARCHAR(10), phonetypeid INT);
INSERT INTO @Phone VALUES
(1, '555-1234', 1),
(2, '555-3456', 2),
(3, '555-8765', 1),
(4, '555-7654', 2),
(5, '555-2222', 3),
(6, '555-3232', 1),
(7, '555-2121', 2),
(8, '555-4444', 3),
(9, '555-5454', 4),
(10, '555-9543', 4);

DECLARE @UserPhone TABLE (userid INT, phoneid INT);
INSERT INTO @UserPhone VALUES
(1, 1),
(1, 2),
(2, 3),
(2, 4),
(2, 5),
(3, 6),
(3, 7),
(3, 8),
(3, 9),
(4, 10);

DECLARE @User TABLE (userid INT, firstname VARCHAR(10), lastname VARCHAR(10));
INSERT INTO @User VALUES
(1, 'John', 'Smith'),
(2, 'Sue', 'Brown'),
(3, 'Rob', 'Green'),
(4, 'Dave', 'Black');

SELECT * FROM
(SELECT U.userid, UD.lastname, UD.firstname, P.phonenumber, T.Name
FROM @UserPhone U INNER JOIN @User UD ON U.userid = UD.userid
INNER JOIN @Phone P ON U.phoneid = P.id
INNER JOIN @PhoneType T ON P.phonetypeid = T.id) A
PIVOT (MAX(phonenumber) FOR NAME IN ([WORK], [HOME], [CELL], [FAX])) T

[/CODE]

littlewing
Starting Member

33 Posts

Posted - 2013-06-07 : 12:37:09

Thanks very much those solutions work very nicely, I think I need to become familiar with Pivot function

Thanks again,
LW

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-07 : 14:23:52

Glad to help.
There are some nice articles on the web pertaining to this topic, here is one:
http://blog.sqlauthority.com/tag/pivot/

quote:
Originally posted by littlewing

Thanks very much those solutions work very nicely, I think I need to become familiar with Pivot function

Thanks again,
LW

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources