Different result in SQL fiddle and in MySQL server/PhpMyadmin

Question

This is the sql fiddle :http://sqlfiddle.com/#!2/e6acc/4

And it shows the right result since I want the duplicate entries to be enumerated.

But when I run in on mysql/phpmyadmin, the result is this:

1515    Abdominal
1100    Hep B Inj Fee
40  1-Ligation
40  1-Ligation
900 1-Suturing Fee
900 1-Suturing Fee
900 1-Suturing Fee
900 1-Suturing Fee

It's all 1. I already restarted my pc. and its still the same. Why is that? I just retrieved the csv tables from my mysql to the fiddle.

edit: Ok this is getting weirder. On my front-end, its working when I view other ID's: enter image description here

But still, when I change the id on my query and enter in on phpmyadmin, it stil shows 1.

What version of MySQL are you running locally? I suppose this goes without saying, but have you double-checked all the tables and records that they for sure match between the Fiddle and your mysql data, and that you are running exactly the same query? — mellamokb, Feb 13 '13 at 22:01
Also, if you run each of the inner queries one by one, do the rows come back in the exact same order between both environments? I notice you have an `ORDER BY` in the first subquery, but not in the second, so arbitrary ordering of the rows (and insertion order) in the mysql tables can possibly make a difference in the result. You want to make sure there is no ambiguity in the order of anything. — mellamokb, Feb 13 '13 at 22:05
@mellamokbtheWise I edited my post. its the exact same query but it has different result in my front-end site too. — jeffmangum, Feb 13 '13 at 22:10
check this line if its right and have exact spelling `@curRow:=CASE WHEN @prevRow = a.Proc THEN @curRow+1 ELSE 1 END AS rn,` — echo_Me, Feb 13 '13 at 22:36
Trying to understand the issue. It works in SQL Fiddle and is also correct when you execute the query through PHP (Assuming this is what you mean by front-end), but not in phpMyAdmin when you paste into the SQL tab (Run SQL query/queries on database)? — neelsg, Feb 18 '13 at 09:33

score 0 · Accepted Answer · answered Feb 18 '13 at 10:13

I don't have any idea why the SQL Fiddle is different than the actual result, but I managed to make it work for me in PHP. Use the following query:

SELECT
  c.procno,
  CONCAT(CASE WHEN cnt > 1 THEN CONCAT(RN,'-') ELSE '' END, t.Proc) Proc
FROM
  (
    SELECT
      @curRow:=CASE WHEN @prevRow = a.Proc THEN @curRow+1 ELSE 1 END AS rn,
      a.Proc,
      a.Procno,
      @prevRow:=Proc grp
    FROM (    
          SELECT
            `incurredcharges`.`procedure_no` procno,
            `c`.`procedure` proc
          FROM
            incurredcharges
            INNER JOIN (
              SELECT `procedure`, `procedure_no` FROM `charges`
              UNION ALL
              SELECT `confinement`, `procedure_no` FROM `confinement`
              UNION ALL
              SELECT `service`, `procedure_no` FROM `ultrasound`
            ) c ON `incurredcharges`.`procedure_no` = c.`procedure_no`
          WHERE `incurredcharges`.`patient_no` = '34'
          ORDER BY `c`.`procedure`
      ) a 
          JOIN (SELECT @curRow:=0, @prevRow:= '') r
  ) t JOIN
  (
          SELECT
            `incurredcharges`.`procedure_no` procno,
            `c`.`procedure` proc, Count(*) cnt
          FROM
            incurredcharges
            INNER JOIN (
              SELECT `procedure`, `procedure_no` FROM `charges`
              UNION ALL
              SELECT `confinement`, `procedure_no` FROM `confinement`
              UNION ALL
              SELECT `service`, `procedure_no` FROM `ultrasound`
            ) c ON `incurredcharges`.`procedure_no` = c.`procedure_no`
          WHERE `incurredcharges`.`patient_no` = '34'
          GROUP BY `incurredcharges`.`procedure_no`,
            `c`.`procedure`
  ) c ON t.proc = c.proc

The only thing I changed was line 27 from JOIN (SELECT @curRow:=0) r to JOIN (SELECT @curRow:=0, @prevRow:= '') r

From this question, I learned about those @vars you can use in queries. Never knew about them and I could have used them before on several occasions. Thanks :) — neelsg, Feb 18 '13 at 10:22

Different result in SQL fiddle and in MySQL server/PhpMyadmin

1 Answers1

Linked