troubleshooting Question

Join 3 MySQL Tables Together

Avatar of Pawel_Kowalski
Pawel_Kowalski asked on
DatabasesMySQL ServerPHP
6 Comments3 Solutions560 ViewsLast Modified:
Hello,

I have a very large database I am dealing with and am having trouble getting results in a single query for 3 database tables.

I attached a code snippet of these 3 tables with sample data.

What I need to do is this:

1. Loop through zSS_ndc_medid
2. JOIN zSS_ndc_medid to RM_MEDID using NDC
3. For each row in zSS_ndc_medid take the NDC from that row and search through zSS_formulary_list for this NDC. However, results must be limited to any row that has a value of provider=RXHUBPBM and List_ID=FSL101 (results wont always be found as there wont always be the NDC from this row in the zSS_formulary_list table).

Currently I have no problem with number 1 or 2. I can properly JOIN the two tables based on NDC and display results. But when I want to search through zSS_formulary_list I have to create another query inside of a loop which is really slow. Is there a way to combine all this in to a single query?

If it's impossible to do this in a single query are there any other optimization tips I can get? I can not change the format of this data, but I can add additional tables.

Help would be much appreciated. Thank you!

zSS_ndc_medid

NDC	|MEDID	|GNI	
-------------------------
1001	|11	|2
1002	|11	|2
1003	|11	|3
1004	|11	|2
1005	|13	|2
1006	|16	|2


zSS_formulary_list

NDC		|List_ID	|Provider	|Status		
-----------------------------------------------------------------
1001		|FSL101		|RXHUBPBM	|2
1002		|FSL101		|RXHUBPBM	|2
1003		|FSL101		|RXHUBPBM	|5
1004		|FSL101		|RXHUBPBM	|2
1001		|FSL102		|RXHUBPBM	|2
1002		|FSL102		|RXHUBPBM	|2
1003		|FSL103		|RXHUBPBM	|5
1004		|FSL104		|RXHUBPBM	|2

RM_MEDID

MED_ID		|Name		|
---------------------------------
11		|One		|
13		|Two		|
16		|Three		|

Open in new window

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros