September 12, 2011 at 8:30 am
Hi,
This is a new thread from my other one (can't remember what it's called now) but it provide's some data too..
I wish to create a table something like this from the script below....
[ID] [fname] [lname] [phnNum]
5 Graham Atlinson 07762000001 patone@test.com
but am currently getting
[ID] [fname] [lname] [phnNum]
5 Graham Atlinson 07762000001
Here's the data to get things going...
CREATE TABLE [dbo].[Patient] (
[dbPatCnt] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[dbPatFirstName] [varchar](15) NULL,
[dbPatLastName] [varchar](25) NULL,
[dbStatus] [int] NULL
)
CREATE TABLE [dbo].[LnkPhone](
[dbPhoneID] [int] NULL,
[dbKeyCnt] [int] NULL
)
CREATE TABLE [dbo].[Phone](
[dbPhoneID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[dbPhoneNumber] [varchar](50) NULL,
[dbPhoneTypeID] [tinyint] NULL
)
set identity_insert dbo.patient on
insert into dbo.patient(dbPatCnt,dbPatFirstName,dbPatLastName, dbStatus)
values(5,'Graham','Atkinson',1)
insert into dbo.patient(dbPatCnt,dbPatFirstName,dbPatLastName, dbStatus)
values(7,'Rebecca','Bailey',1)
insert into dbo.patient(dbPatCnt,dbPatFirstName,dbPatLastName, dbStatus)
values(8,'Zoe','Bell',1)
set identity_insert dbo.patient off
insert into dbo.lnkphone (dbPhoneID,dbKeyCnt)
values (6770,5)
insert into dbo.lnkphone (dbPhoneID,dbKeyCnt)
values (7628,5)
insert into dbo.lnkphone (dbPhoneID,dbKeyCnt)
values (756,7)
insert into dbo.lnkphone (dbPhoneID,dbKeyCnt)
values (5839, 7)
insert into dbo.lnkphone (dbPhoneID,dbKeyCnt)
values (7052,8)
insert into dbo.lnkphone (dbPhoneID,dbKeyCnt)
values ( 5,8)
set identity_insert phone on
insert into dbo.phone (dbPhoneID,dbPhoneNumber,dbPhoneTypeID)
values(6770,'07762000001',5)
insert into dbo.phone (dbPhoneID,dbPhoneNumber,dbPhoneTypeID)
values(7628,'patone@test.com',9)
insert into dbo.phone (dbPhoneID,dbPhoneNumber,dbPhoneTypeID)
values(756,'07762000002',5)
insert into dbo.phone (dbPhoneID,dbPhoneNumber,dbPhoneTypeID)
values(5839,'pattwo@test.com',9)
insert into dbo.phone (dbPhoneID,dbPhoneNumber,dbPhoneTypeID)
values(7052,'07762000003',5)
insert into dbo.phone (dbPhoneID,dbPhoneNumber,dbPhoneTypeID)
values(5,'patthree@test.com',9)
set identity_insert phone off
and here's the script (thanks to Tom)
select
patient.dbpatcnt as id,
patient.dbpatfirstname as fname,
patient.dbpatlastname as lname,
Zphone.phnNum,
Zphone.
from patient
inner join
lnkphone on lnkphone.dbkeycnt = patient.dbpatcnt
inner join
(select A.dbphoneid, A.dbphonenumber as phnNum,
isNull(B.dbphonenumber,'') as
from (select dbphoneid, dbphonenumber
from phone where dbphonetypeid = 5
) A
left join
(select dbphoneid, dbphonenumber
from phone where dbphonetypeid = 9) B
on A.dbphoneid = B.dbphoneid
)Zphone on Zphone.dbphoneid = lnkphone.dbphoneid
where patient.dbstatus <> 13
order by patient.dbpatcnt
All the above is just part of a larger real world project and here I'm just interested in getting one row for each patient where they have a phntypeid of EITHER 5 (moby) or 9 (email) or in the above sample data both.
mucho thanko
September 12, 2011 at 8:52 am
OK, with proper table definitions and test data it becomes clear that there are multiple lnkphone records per patient. I had assumed only one. New code to follow shortly.
Tom
September 12, 2011 at 9:44 am
OK, here is new code. I haven't tested it with cases where the email is missing or the phone number is missing, so let me know if there is a probem with that.
select
patient.dbpatcnt as id,
patient.dbpatfirstname as fname,
patient.dbpatlastname as lname,
coalesce(Z.phNum,'') phnNum,
coalesce(Z.email,'') email
from
(select coalesce(Phone5.dbkeycnt,Phone9.dbkeycnt) dbkeycnt, phnum, email
from (select lnkphone.dbphoneid, lnkphone.dbkeycnt, dbphonenumber as phnum
from lnkphone inner join
(select dbphoneid, dbphonenumber from phone where dbphonetypeid = 5) A
on lnkphone.dbphoneid = A.dbphoneid) Phone5
full join
(select lnkphone.dbphoneid, lnkphone.dbkeycnt, dbphonenumber as email
from lnkphone inner join
(select dbphoneid, dbphonenumber from phone where dbphonetypeid = 9) B
on lnkphone.dbphoneid = B.dbphoneid) Phone9
on Phone5.dbkeycnt=Phone9.dbkeycnt
) Z
inner join patient on dbkeycnt = dbpatcnt
Tom
September 13, 2011 at 2:27 am
IT WORKS!
Thanks very much for your help Tom...pour yourself a Sangrea and kick back for the rest of the day 😉
September 13, 2011 at 3:50 am
Hi,
Another way:-
select distinct a.dbpatcnt, a.dbpatfirstname, a.dbpatlastname,
a.dbphonenumber, dbphonenumber1
from
(select dbpatcnt, dbpatfirstname, dbpatlastname,
dbphonetypeid, dbphonenumber
from dbo.patient P
inner join dbo.LnkPhone L
on dbpatcnt = dbkeycnt
inner join dbo.phone Ph
on l.dbphoneid = ph.dbphoneid) as A,
(select dbpatcnt, dbpatfirstname, dbpatlastname,
dbphonetypeid, dbphonenumber as dbphonenumber1
from dbo.patient P
inner join dbo.LnkPhone L
on dbpatcnt = dbkeycnt
inner join dbo.phone Ph
on l.dbphoneid = ph.dbphoneid) as B
where a.dbpatcnt = b.dbpatcnt
and a.dbpatfirstname = b.dbpatfirstname
and a.dbpatlastname = b.dbpatlastname
and a.dbphonenumber <> dbphonenumber1
and dbphonenumber1 not like '%@%'
Regards
PG
September 13, 2011 at 4:03 am
Slighty change in my code:-
select distinct a.dbpatcnt, a.dbpatfirstname, a.dbpatlastname,
a.dbphonenumber, dbphonenumber1
from
(select dbpatcnt, dbpatfirstname, dbpatlastname,
dbphonetypeid, dbphonenumber
from dbo.patient P
inner join dbo.LnkPhone L
on dbpatcnt = dbkeycnt
inner join dbo.phone Ph
on l.dbphoneid = ph.dbphoneid
and dbphonetypeid = 9) as A
full join
(select dbpatcnt, dbpatfirstname, dbpatlastname,
dbphonetypeid, dbphonenumber as dbphonenumber1
from dbo.patient P
inner join dbo.LnkPhone L
on dbpatcnt = dbkeycnt
inner join dbo.phone Ph
on l.dbphoneid = ph.dbphoneid
and dbphonetypeid = 5) as B
on a.dbpatcnt = b.dbpatcnt
and a.dbpatfirstname = b.dbpatfirstname
and a.dbpatlastname = b.dbpatlastname
and a.dbphonenumber <> dbphonenumber1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply