running32
asked on
Select query
Can you please tell me how I can run this query so that if there is not a record in tblPatient I do not get 3 records for one person for spanish/English/Null. thanks
SELECT Distinct lastname, firstname,case when bytlanguage = 2 then 'English' when bytlanguage = 3 THEN 'Spanish' when bytlanguage = 1 THEN 'English'
when bytlanguage = 4 THEN 'English' end as 'Language', appt.phone, CONVERT(VARCHAR,APPTDATE, 101) as apptdate, CONVERT(VARCHAR(10), time_hour, 120) + ':' + CONVERT(VARCHAR(10), time_minute, 120) as time,apptreason, name_last, name_first FROM DDB_APPT_BASE as appt inner join ddb_pat_base as pat on appt.patid = pat.patid inner join ddb_rsc_base as rsc on appt.prprovid = rsc.urscid inner join tblpatient as p on appt.chart = p.strpatientid
WHERE CONVERT(VARCHAR,APPTDATE, 101) = CONVERT(VARCHAR,DateAdd(dd
CASE WHEN DATEPART(weekday, getdate()) IN ( 5,6) THEN 4 ELSE 2 END
, GetDate()), 101) and rsc.defaultclinic = 30
ORDER BY APPTDATE DESC
SELECT Distinct lastname, firstname,case when bytlanguage = 2 then 'English' when bytlanguage = 3 THEN 'Spanish' when bytlanguage = 1 THEN 'English'
when bytlanguage = 4 THEN 'English' end as 'Language', appt.phone, CONVERT(VARCHAR,APPTDATE, 101) as apptdate, CONVERT(VARCHAR(10), time_hour, 120) + ':' + CONVERT(VARCHAR(10), time_minute, 120) as time,apptreason, name_last, name_first FROM DDB_APPT_BASE as appt inner join ddb_pat_base as pat on appt.patid = pat.patid inner join ddb_rsc_base as rsc on appt.prprovid = rsc.urscid inner join tblpatient as p on appt.chart = p.strpatientid
WHERE CONVERT(VARCHAR,APPTDATE, 101) = CONVERT(VARCHAR,DateAdd(dd
CASE WHEN DATEPART(weekday, getdate()) IN ( 5,6) THEN 4 ELSE 2 END
, GetDate()), 101) and rsc.defaultclinic = 30
ORDER BY APPTDATE DESC
ASKER
They are still showing up
Ash K NULL 970123455 04/05/2007 14:0 CompEx, 2BWX, ProphyCh, FluoridCh Eckhardt Bradley
Ash K English 970123455 04/05/2007 14:0 CompEx, 2BWX, ProphyCh, FluoridCh Eckhardt Bradley
Ash K Spanish 970123455 04/05/2007 14:0 CompEx, 2BWX, ProphyCh, FluoridCh Eckhardt Bradley
Bea T NULL 720123455 04/05/2007 10:0 FullDebrd Sleight Bev
Bea T English 720123455 04/05/2007 10:0 FullDebrd Sleight Bev
Ash K NULL 970123455 04/05/2007 14:0 CompEx, 2BWX, ProphyCh, FluoridCh Eckhardt Bradley
Ash K English 970123455 04/05/2007 14:0 CompEx, 2BWX, ProphyCh, FluoridCh Eckhardt Bradley
Ash K Spanish 970123455 04/05/2007 14:0 CompEx, 2BWX, ProphyCh, FluoridCh Eckhardt Bradley
Bea T NULL 720123455 04/05/2007 10:0 FullDebrd Sleight Bev
Bea T English 720123455 04/05/2007 10:0 FullDebrd Sleight Bev
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What table contains the byLanguage column?
ASKER
tblpatient
for each language you are getting there must be a row in tblpatient...
please explain in business english what you are attempting to achieve..,.
indicate the proper joins between your tables...
indicate what multiple rows mean for each table...
please explain in business english what you are attempting to achieve..,.
indicate the proper joins between your tables...
indicate what multiple rows mean for each table...
ASKER
the field bytlanguage is stored as 1, 2, 3, 4 so I need to write out the actual language so I need to put the case statement in.
tblPatient contains the bytlanguage and is joined to DDB_APPT_BASE by chart # and strpatientid
DDB_APPT_BASE stores all the appointment and is joined by patid
ddb_rsc_base stored the provider name and is joined to DDB_APPT_BASE by patid
If there is no client that matches chart # =strpatientid the case statment writes the records out 3 rows. Null, Spanish and English. I need to identify if the client does not have a record in tblPatient and write out English instead.
Thanks
tblPatient contains the bytlanguage and is joined to DDB_APPT_BASE by chart # and strpatientid
DDB_APPT_BASE stores all the appointment and is joined by patid
ddb_rsc_base stored the provider name and is joined to DDB_APPT_BASE by patid
If there is no client that matches chart # =strpatientid the case statment writes the records out 3 rows. Null, Spanish and English. I need to identify if the client does not have a record in tblPatient and write out English instead.
Thanks
>tblPatient contains the bytlanguage and is joined to DDB_APPT_BASE by chart # and strpatientid
I think that is your problem. Chart # contains something besides patientid's otherwise, you would not return any rows when strpatientid is missing. Basically, you appear to be matching chart # (whatever that is) to some unrelated patient.
Note, the case statement is NOT responsible for writing out the 3 unexpected rows. That's a problem in your WHERE clause. However, to get the case statement to force "English" for a missing row, you need to do two things (after you get the join working properly)
1. Outer join to the patient table -- otherwise you will get no rows for the client without a patient record
2. case bylanguage when 3 THEN 'Spanish' ELSE 'English' end as 'Language' -- otherwise you will get no language for the client without a patient record
I think that is your problem. Chart # contains something besides patientid's otherwise, you would not return any rows when strpatientid is missing. Basically, you appear to be matching chart # (whatever that is) to some unrelated patient.
Note, the case statement is NOT responsible for writing out the 3 unexpected rows. That's a problem in your WHERE clause. However, to get the case statement to force "English" for a missing row, you need to do two things (after you get the join working properly)
1. Outer join to the patient table -- otherwise you will get no rows for the client without a patient record
2. case bylanguage when 3 THEN 'Spanish' ELSE 'English' end as 'Language' -- otherwise you will get no language for the client without a patient record
I meant, that's a problem in your FROM Clause, i.e. the join criteria
ASKER
In the query I am using the chart and strpatientid are both blank for the records that have been written out twice. Once for Spanish and once for English. How can I do a join when there is the potential for both values to be null?
thanks
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
" inner join tblpatient as p on appt.chart = p.strpatientid "
try
" inner join tblpatient as p on p.strpatientid = appt.chart "
Check this article, it explains why I say this:
(http://database.ittoolbox.com/documents/popular-q-and-a/inner-and-outer-join-sql-statements-2108)
Greetings