troubleshooting Question

Getting Data From 3 mySQL Tables

Avatar of Pawel_Kowalski
Pawel_Kowalski asked on
DatabasesPHPMySQL Server
14 Comments2 Solutions633 ViewsLast Modified:
Hello, I have a mySQL database with the following tables:

zSS_ndc_medid
RMIID1_MED
zSS_formulary_list

The database structure looks a bit like this:

zSS_ndc_medid:

MEDID      |NDC
-------------
1      |2222
1      |3333
1      |4444
2      |5555

RMIID1_MED:

MEDID      |MED_MEDID_DESC
-----------------------
1      |Tylenol
2      |Bayer

zSS_formulary_list

NDC      |formulary_status      |list_id      |provider      |MEDID
----------------------------------------------------------------------
22222      |2                  |FSL101            |PBM            |1
22222      |3                  |FSL101            |PBM            |1


So far with some great help from here I have the following query in my PHP script:

SELECT 
    a.NDC, a.MEDID, a.BN, a.GNI, b.MED_STRENGTH, b.MED_STRENGTH_UOM, b.MED_MEDID_DESC, b.MEDID, b.MED_REF_FED_LEGEND_IND, b.GENERIC_MEDID, c.NDC, c.provider, c.list_id, c.formulary_status
FROM zSS_ndc_medid a
JOIN RMIID1_MED b ON a.MEDID = b.MEDID
LEFT JOIN zSS_formulary_list c ON c.NDC = a.NDC
   AND c.list_id = 'FSL101' AND c.provider = 'RXHUBPBM' 

This works fine most of the time. However, when I have a case such as the one above where there are multiple NDC values with different status in the zSS_formulary_list table I can only get one status, unfortunately I need to get all of them.

Looping through each record isn't really an option since I am dealing with large amounts of data. Is there a way to devise a SQL query that can do this all in a single query? Any help would be much appreciated. Thank you.
ASKER CERTIFIED SOLUTION
dqmq

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 14 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 2 Answers and 14 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