current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have searched high an low for a solution, but I just can't get my head wrapped around how to set this up.

I have a table called "cals". It contains a column called "rep_code". Which contains a certain value identifying the cal, ie. "EFM","LM" etc. The "cals" list of records are linked to wells by "well_id" which is the indexer on another table called "wells". Now each record in the wells table is linked by "run_id" which is the indexer on another table called "runs".

Wells table:

Indexer  |   run_id
1        |     5
2        |     3
3        |     5
4        |     2

cals table:

Indexer  |   well_id   | rep_code
1        |     3       |    LM
2        |     4       |    EFM
3        |     1       |    LM
4        |     3       |    EFM

Now when I view the list of runs, I want to display the total number reports in the run that have "EFM" under "rep_code", and total number of "LM" codes etc. So on the list of runs, the run with ID of 5 should say total of 2 "LM" reports and 1 "EFM". Run number 2 should just have 1 "EFM and 0 "LM"

Basically, what I think I need to do is " Join wells into cals adding run_id where well_id = $well_id from run list"...I think, every time I read this I get a little confused. I am new to "join" so I apologize.

I have managed to put together this bit of code to count each occurrence which works for one well, but cannot seem to get my head wrapped around putting it all into a functional statement for all wells in the run. 

$sql = "SELECT 
COUNT(CASE WHEN rep_code='LM' THEN 1 END) AS LM_tot,
COUNT(CASE WHEN rep_code='EFM' THEN 1 END) AS EFM_tot,
COUNT(CASE WHEN rep_code='GM' THEN 1 END) AS GM_tot
FROM cals WHERE well_id = '$well_id'";

$query = @mysql_query($sql);
$result = @mysql_fetch_array($query);

$LM_tot = $result['LM_tot'];  
$EFM_tot = $result['EFM_tot'];
$GM_tot = $result['GM_tot'];

I also started this join, but I haven't gotten it to work yet.

sql = "SELECT
COUNT(CASE WHEN rep_code='LM' THEN 1 END) AS LM_tot,
COUNT(CASE WHEN rep_code='EFM' THEN 1 END) AS EFM_tot,
COUNT(CASE WHEN rep_code='GM' THEN 1 END) AS GM_tot
FROM cals, wells INNER JOIN wells ON cals.well_id = wells.indexer 
WHERE run_id = '$run_id'";

I hope I have explained this clear enough.

share|improve this question
    
It would help if you put the tables sctructure so we can see the join you are talking about. –  Jorge Campos Dec 3 '13 at 19:36
    
Hope that helps –  Tyler Dec 3 '13 at 20:17
    
Please, see my edit. –  Jorge Campos Dec 3 '13 at 21:15
    
Thanks Jorge, however, i need to merge the run_id into the well_id column on the cals table (see my join edit). I've been trying a lot of things. Seems like it should be simple... –  Tyler Dec 3 '13 at 21:40
add comment

2 Answers

up vote 0 down vote

Putting apart the join you are talking to put your query to work you just need to change a bit:

$sql = "SELECT 
           SUM(CASE WHEN rep_code='LM' THEN 1 ELSE 0 END) AS LM_tot,
           SUM(CASE WHEN rep_code='EFM' THEN 1 ELSE 0 END) AS EFM_tot,
           SUM(CASE WHEN rep_code='GM' THEN 1 ELSE 0 END) AS GM_tot
          FROM cals 
               INNER JOIN 
               wells ON cals.well_id = wells.indexer
         WHERE cals.well_id = '$well_id'";

If there more things to add, update your question with your tables structure and put some example data and the desired result, then I will update my answer.

share|improve this answer
add comment
up vote 0 down vote accepted

This is what i ended up using, and works great.

$sql = "SELECT
COUNT(CASE WHEN rep_code='LM' THEN 1 END) AS LM_tot,
COUNT(CASE WHEN rep_code='EFM' THEN 1 END) AS EFM_tot,
COUNT(CASE WHEN rep_code='GM' THEN 1 END) AS GM_tot
FROM cals 
INNER JOIN 
wells ON cals.well_id = wells.indexer
WHERE run_id = '$run_id'";
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.