Solved

Select query

Posted on 2007-04-03
11
242 Views
Last Modified: 2010-03-20
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
0
Comment
Question by:running32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 7

Expert Comment

by:JuanCarniglia
ID: 18843561
Instead of

"    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
0
 

Author Comment

by:running32
ID: 18843615
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
0
 
LVL 7

Assisted Solution

by:JuanCarniglia
JuanCarniglia earned 150 total points
ID: 18843671
Ok, try:

"   
    FROM DDB_APPT_BASE as appt
    inner join ddb_pat_base as pat on pat.patid = appt.patid
    inner join ddb_rsc_base as rsc on rsc.urscid  = appt.prprovid
    inner join tblpatient as p on p.strpatientid = appt.chart
"

Instead of

"  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   "

Greetings
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 42

Expert Comment

by:dqmq
ID: 18843754
What table contains the byLanguage column?
0
 

Author Comment

by:running32
ID: 18843884
tblpatient
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18844059
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...



0
 

Author Comment

by:running32
ID: 18844345
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
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18844699
>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



0
 
LVL 42

Expert Comment

by:dqmq
ID: 18845247
I meant, that's a problem in your FROM Clause, i.e. the join criteria
0
 

Author Comment

by:running32
ID: 18845718
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
0
 
LVL 42

Accepted Solution

by:
dqmq earned 350 total points
ID: 18846202
They are not both NULL. In fact, if either is NULL you will not produce a row in the result set. It looks to me like appt.chart contains something, perhaps an empty string and it is matching with exactly that same value in 3 rows of tblPatient.

First, you must find out exactly what is in chart # when there isn't a patient record. Do this:
Select  Chart, ISNULL(Chart,'NULL'), LEN(chart), ASCII(chart)
Then locate one of the "problem" rows and see what it shows.
Then delete the 'Spanish' and 'NULL' rows from tblPatient having that patientID.  I think if you look at those two rows you will see they are not real patients.  

0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question