September 11, 2011 at 11:01 am
I think I get it: is this what you mean?
The phone table contains two lines for each patient - one marked (in the dbphonetypeid column) 9 which contains the email address but not the phone number, an one marked 5 that contains the phone mumber but not the email address; and you want to build a result with 1 row for each patient that contains both the email and the phone number and doesn't include the mark.
That should be easy enough to do. But maybe not every patient has a rown marked 9 (not every patient has an email address) so the code should cater for that.
The code to do it is straightforwards too.
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.dbstatusid <> 13
order by patient.dbpatcnt
drop table phone, lnkphone, patient
Try that and see what you get, and let us know what happens. I haven't tested it, as you've supplied no test data.
If this query wasn't posted in the SQLS 7/2000 forum the code would use CTEs for the intermediate derived tables A, B and Zphone, because it would look a bit clearer that way, but the algorithm would be the same.
EDIT: when you edit a post using the edit button at the top right, the button for saying you've finished editing and posting the edited text is the one labelled "Edit Post" near the bottom left (just above the Post Options block)
Tom
September 11, 2011 at 11:09 am
cheers tom. I've tried the sql and the email field isn't getting populated though. Both of these have 2 related records each in the Phone table for type 5 (mobile) and type 9 (email)
idfnamelnamephnNum email
5GrahamAtkinson07762 540001
7RebeccaBailey07762 540002
although it's one record for each patient which is good
how's best to provide test data on here? just cut and paste?
September 11, 2011 at 1:14 pm
mattech06 (9/11/2011)
cheers tom. I've tried the sql and the email field isn't getting populated though. Both of these have 2 related records each in the Phone table for type 5 (mobile) and type 9 (email)
idfnamelnamephnNum email
5GrahamAtkinson07762 540001
7RebeccaBailey07762 540002
That's odd. Is the email address actually in the dbphonenumber column of the row with type 9?
although it's one record for each patient which is good
how's best to provide test data on here? just cut and paste?
Jeff Moden wrote an article about four years back on providing test data and other useful information for questions in these forums. Here[/url] is a link to it.
Tom
September 12, 2011 at 2:45 am
September 12, 2011 at 4:14 am
is it something to do with the left join part of your sql Tom. It doesn't seem to create or populate the email column? ....the phnytpeid is 9 for an email.
September 12, 2011 at 8:16 am
OK, I made some guesses at table definitions, as follows:-
'create table patient (
dbpatcnt int, dbpatfirstname varchar(30),dbpatlastname varchar(30), dbstatusid int)
create table lnkphone (dbkeycnt int, dbphoneid int)
create table phone (dbphoneid int, dbphonenumber varchar(48),dbphonetypeid int)
I can see that phone is wrong as the example you gave of an entry with type 9 must have 'Graham Atkinson' as either one of two extra columns (else the email address contains spaces, so is not a valid email address), the other definitions probably have missing columns too, and probably all the lengths are wrong as well. Although none of that should make any difference, maybe I've missed something and it does: so it will be useful if you provide correct table definitions so that we can elimainate that as a potential issue.
Also, I invented some test data:
insert patient values (5,'Graham', 'Atkinson',1)
insert phone values (5, 'Graham Atkinson patientne@test.com',9)
insert phone values (5, '07762 540001',5)
insert lnkphone values (5,5)
This also is known to be incorrect, because the column sets aren't right (hence the invalid email address with spaces in it, and anyway 1 for the sbdbstatus in the patient record is just pure guesswork - except for not b eing 13). But again, that shouldn't make any difference to the logic.
When I run the code against that test data I get
id fname lname phnum email
5GrahamAtkinson07762 540001Graham Atkinson patientne@test.com
which is inconsistent with what you are getting with the same code. So something IS making a difference, and it will be useful if you provide some test data (in the form of insert statements) - as you can see, just 1 patient row, two phone rows for that patient (one each of types 5 and 9) and the corresponding lnkphone row should be enough to show the problem.
edit: remove previous mistaken edit
Tom
September 12, 2011 at 8:19 am
Hi,
I'm starting a new thread for this problem with the data for you to use..in case any other chaps want to have a look at it as well...I'm going to title it 'create one row from two rows query'
thanks
September 12, 2011 at 8:34 am
Hi Tom,
sorry didn't see your last thread before I posted mine..hopefully the new thread will push us closer to Moscow (curious analogy no.162)
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply