asked on 5/17/2011

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:

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

Databases MySQL Server PHP

Last Comment

Kevin Cross

5/17/2011

ASKER CERTIFIED SOLUTION

Kevin Cross

5/17/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Pawel_Kowalski

5/17/2011

ASKER

Thank you for the fast reply, the query seems to be working. However, since I am dealing with around 130,000 records it's really slow. To get 10 records it takes about 20 seconds. That way I am looking at about 6 hours of processing. Is there any possible way to speed this up? This is something that is done through console and doesn't need to be instant, but 6 hours is a bit much.

Since the table names and fields are slightly different in the production databse my actual query is as follows:

SELECT 
    a.NDC, a.MEDID, a.BN, a.GNI, b.MED_MEDID_DESC, b.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'

Open in new window

Thanks again for the help.

SOLUTION

GreatGerm

5/17/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

Kevin Cross

5/17/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Pawel_Kowalski

5/17/2011

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.

Kevin Cross

5/17/2011

Try this, then if it doesn't work, we'll be around to help when you open your related question.

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'
;

Open in new window

Glad we could help on this one.

Best regards and happy coding,
Kevin