[ad_1]
\n<\/p>\n
The first key thing is to understand what data you have. Here in this case, you have four tables<\/p>\n
Find the list of all the patients who visited all orthopedists (specialty) associated with their Insurance Companies.<\/p>\n
Generally, the requirements might be a bit overwhelming when you look at them on the whole. Let’s split the requirements into smaller components to understand what you need to do.<\/p>\n
You need to identify your main goal, here in this case to identify the list of patients. So, query the Patient table first.<\/p>\n<\/li>\n
You have the patients, actually all of them but we need to find which of these patients visited the doctors. Let’s not worry about whether the doctor is an Orthopedist or not. We just need the list of patients and the doctors they have visited. There is no mapping between Patient and Doctors table. To find out this information,<\/p>\n
Join the Patient table with Visits table on the correct key field.<\/em><\/p>\n Then join the output with the Doctors table on the correct key field.<\/em><\/p>\n<\/li>\n If you have done the join correctly, you should now have the list of all the patients and the doctors that they have visited. If you used Now, you have all the patients and the doctors whom they have visited. However, the requirement is to find only the doctors who are Orthopedists<\/em>. So, apply the condition to filter the result to give only the desired result.<\/p>\n<\/li>\n You have now achieved the requirements as split into smaller components in part a<\/em> and part b<\/em>. You still need to filter it by the insurance companies. Here is the tricky part, the requirement doesn’t say that you need to display the insurance company, so we don’t have to use the table InsuranceCompanies. Your next question will Find the count of unique Orthopedists<\/em> that each patient has visited.<\/p>\n<\/li>\n Here is the part that can be done in many ways, one of the way of doing this would be to add a sub query that would be your fourth column in the output. This sub query would query the table Doctors and filter by speciality = ‘Orthopedist’. In addition to that filter, you also have to filter by matching the insurance company on the inner table with the insurance company id on the Patients table that is on the main query. This subquery will return the count of all the Orthopedists for insurance company id that matches the patient’s data.<\/p>\n<\/li>\n You should now have the fields If you follow the above logic, you should have this.<\/p>\n<\/li>\n<\/ol>\n List of patients who have visited all the doctors<\/p>\n Filtered by only doctors, whose are Orthopedists<\/p>\n Filtered by patients and doctors sharing the same insurance company information.<\/p>\n Again, the whole output is then filtered by the two count fields found inside the derived table output.<\/p>\n I am confident that you can do this easily.<\/p>\n Don’t hesitate to post your questions as I have provided one of the many ways how this logic can be implemented. I am sure that there are many ways to implement this in a far better manner.<\/p>\n Please refer @Ofek Ron’s answer for the correct query that produces the desired output. I didn’t write any part of the query. It was all OP’s effort.<\/p>\n<\/p><\/div>\nLEFT OUTER JOIN<\/code>, you will find even the patients who had never visited a doctor. If you used RIGHT OUTER JOIN<\/code>, you will find only the patients who visited a doctor.<\/p>\n<\/li>\n'How am I going to filter the results?'<\/code>. Valid point. Find out if any of the three tables Patient<\/code>, Doctor<\/code> and Visits<\/code> contain the insurance company information. Patient<\/code> and Doctors<\/code> have a common field. Join that common field to filter the result.<\/p>\n<\/li>\npatient id<\/code>, patient name<\/code>, patients visits count<\/code> and the total number of Orthopedists in same insurance company<\/code> from the sub query. You can then add an outer join that will filter the results from this derived table on the fields where patients visits count<\/code> matches with total number of Orthopedists in same insurance company<\/code>. I am not saying this is the best approach. This is one approach that I can think of.<\/p>\n<\/li>\nThe ball is in your court:<\/h3>\n
\n
If you stumble…<\/h3>\n
comments to this answer<\/code>, Others and I will be glad to assist you.<\/p>\nDisclaimer<\/h3>\n
Outcome:<\/h1>\n