Pawel_Kowalski
asked on
Join 3 MySQL Tables Together
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:
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!
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 |
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much for the help, indexing fixed the speed issue and everything else seems to be working well. I am not getting some NDC results for some of the rows as expected but I think I can work this out, if not I'll start another questions. Thansk again.
Try this, then if it doesn't work, we'll be around to help when you open your related question.
Glad we could help on this one.
Best regards and happy coding,
Kevin
SELECT {column list}
FROM zSS_ndc_media a
LEFT JOIN RM_MEDID b ON a.MEDID = b.MED_ID
LEFT JOIN zSS_formulary_list c ON c.NDC = a.NDC
AND c.List_ID = 'FSL101' AND c.Provider = 'RXHUBPBM'
;
Glad we could help on this one.
Best regards and happy coding,
Kevin
ASKER
Since the table names and fields are slightly different in the production databse my actual query is as follows:
Open in new window
Thanks again for the help.