Query for Insurance and Employer

[josiedds]

I'm looking to run a report that shows who the employers are for a particular insurance.

For example, which employers have Metlife as their carrier. I've tried a few different query examples but no dice.

Any ideas?

[cmcgehee]

Sure, here you go:

Code: Select all

/*List all employers for a particular insurance*/
SET @Carrier='%metlife%';
SELECT c.CarrierName, e.EmpName
FROM insplan ip
INNER JOIN carrier c ON c.CarrierNum=ip.CarrierNum
	AND c.CarrierName LIKE @Carrier
INNER JOIN employer e ON ip.EmployerNum=e.EmployerNum
GROUP BY e.EmployerNum
ORDER BY c.CarrierName,e.EmpName

[josiedds]

That worked great. Thank you.
Is there a tutorial on how to write these queries?

Cuz now I'd like to take it one step further... Can that report give me how many patients are covered by that employer? And can I make it list only active patients?

I'd love to be able to know how to write these...
Thanks

[cmcgehee]

We do have a page that gives an introduction on how to write queries: http://opendental.com/manual/querysql.html.

[JasonL]

I'd love to be able to know how to write these...

Along with the manual page that Chris mentioned there are plenty of free online resources that will teach you some of the basics of SQL/MySQL. W3 Schools has some interactive features that allow you to play with a fake database (good for beginners) and the MySQL reference manual is very detailed (in most places). Whenever you're stuck on something though - you can use the Query Examples page as a guide on how to retrieve information. Hope this helps!

MySQL Reference Manual: https://dev.mysql.com/doc/refman/5.5/en/
W3 Schools: https://www.w3schools.com/SQL/
Query Examples: http://opendentalsoft.com:1942/ODQueryL ... yList.aspx