Query showing patients by specific provider?

For users or potential users.
Post Reply
Craig-DMA
Posts: 11
Joined: Fri May 20, 2016 6:54 am

Query showing patients by specific provider?

Post by Craig-DMA » Mon Aug 29, 2016 8:02 am

I wish there was a like a key somewhere to see all the commands you could input for the queries.

I'm looking for a query to show patients names and addresses for a specific doctor and I seem to having a hard time putting one together. The kicker is the doc has 2 profiles as Dr. T/Dr. T 2.
I'd love to figure it out by myself but if anyone has any help that'd be great too

Craig-DMA
Posts: 11
Joined: Fri May 20, 2016 6:54 am

Re: Query showing patients by specific provider?

Post by Craig-DMA » Mon Aug 29, 2016 8:49 am

I mean it's basically this but I want it to select a specific provider? I keep getting all kinds of errors when trying to write the code:

SELECT patient.LName,patient.FName,patient.MiddleI,patient.Address,patient.Address2,patient.City,patient.State,patient.Zip,patient.PriProv FROM patient

So instead of patient.priprov how do I edit it to say Dr. T, Dr. T 2

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Query showing patients by specific provider?

Post by jsalmon » Mon Aug 29, 2016 9:14 am

Out of our hundreds of query examples that we write for customers I'm fairly certain this query or one very similar to it will be in our list. If not, there will be some examples on how to achieve what you are wanting (which is a JOIN and a WHERE clause added to your current query).
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

User avatar
dgraffeo
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: Query showing patients by specific provider?

Post by dgraffeo » Mon Aug 29, 2016 9:17 am

Writing queries is complicated, but can be done with a bit of practice. I'm sure there's lots of tutorials out there on how to do things. That being said, for your particular case you'd want an INNER JOIN on the provider table using the patient's PriProv and the provider's ProvNum. Essentially, it will go through each row in the patient table, grab the PriProv value, and match it with an entry in the provider table, giving you the patient as well as its corresponding provider.
Now that we have the patient matched with a provider, we want to filter out the entries we don't want. If you knew the provider's ProvNum you could use that, but you gave me an example of a provider that has two abbreviations, Dr. T and Dr. T 2. This requires a WHERE clause, using LIKE to match the abbreviations. The WHERE will take the patient and provider combination that we got previously and remove things that don't match what's stated in the WHERE clause. In the end, it should look something like this:

SELECT patient.LName, patient.FName, patient.MiddleI, patient.Address, patient.Address2, patient.City, patient.State, patient.Zip, provider.Abbr FROM patient INNER JOIN provider ON patient.PriProv=provider.ProvNum WHERE provider.Abbr LIKE '%Dr. T%' OR provider.Abbr LIKE '%Dr. T 2%'

We return all the patient information, and then the abbreviation from its matched provider since the Abbr exists on the provider segment of the matched row. To view the abbreviation, we specify "provider.Abbr" as a column we want to view. In addition, we only care about patient/provider matches that have a provider Abbr that's like "Dr. T" or that's like "Dr. T 2".

If you simply did "SELECT * FROM..." instead of specifying columns, you will see both the patient's information as well as its matched provider's information, in case you wanted to see what "Matching a patient to a provider" looks like.

Hope this helps!
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com

Craig-DMA
Posts: 11
Joined: Fri May 20, 2016 6:54 am

Re: Query showing patients by specific provider?

Post by Craig-DMA » Mon Aug 29, 2016 12:55 pm

dgraffeo wrote:Writing queries is complicated, but can be done with a bit of practice. I'm sure there's lots of tutorials out there on how to do things. That being said, for your particular case you'd want an INNER JOIN on the provider table using the patient's PriProv and the provider's ProvNum. Essentially, it will go through each row in the patient table, grab the PriProv value, and match it with an entry in the provider table, giving you the patient as well as its corresponding provider.
Now that we have the patient matched with a provider, we want to filter out the entries we don't want. If you knew the provider's ProvNum you could use that, but you gave me an example of a provider that has two abbreviations, Dr. T and Dr. T 2. This requires a WHERE clause, using LIKE to match the abbreviations. The WHERE will take the patient and provider combination that we got previously and remove things that don't match what's stated in the WHERE clause. In the end, it should look something like this:

SELECT patient.LName, patient.FName, patient.MiddleI, patient.Address, patient.Address2, patient.City, patient.State, patient.Zip, provider.Abbr FROM patient INNER JOIN provider ON patient.PriProv=provider.ProvNum WHERE provider.Abbr LIKE '%Dr. T%' OR provider.Abbr LIKE '%Dr. T 2%'

We return all the patient information, and then the abbreviation from its matched provider since the Abbr exists on the provider segment of the matched row. To view the abbreviation, we specify "provider.Abbr" as a column we want to view. In addition, we only care about patient/provider matches that have a provider Abbr that's like "Dr. T" or that's like "Dr. T 2".

If you simply did "SELECT * FROM..." instead of specifying columns, you will see both the patient's information as well as its matched provider's information, in case you wanted to see what "Matching a patient to a provider" looks like.

Hope this helps!
.

This was so informative and helpful, I really appreciate it. I knew about the other queries and I spent awhile looking through them and didnt find a preset one for this and trying to put it together myself was frustrating with constant errors and not understanding fully. It's really helpful that you took the time to explain it so I understand it better moving forward. Thanks again!

Post Reply