Solved

Select query

Posted on 2007-04-03
11
215 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
  • 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
 
LVL 42

Expert Comment

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

Author Comment

by:running32
ID: 18843884
tblpatient
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now