MySQL: Join table iteself. Unknow column

Question

To go Mad!!!!

On Access works and on MySQL don't want. And I take the trick on a post here.

Here the query:

SELECT A.B_ID,
       B.CNT_B,
       B.MAX_PRICE,
       B.SUM_AB,
       B.SUM_C_AB,
       B.UNIQUE_B,
       B.Costi,
       B.COUNTDOWN,
       A.AUCT_ID
FROM MONITOR AS A
INNER JOIN
  (SELECT B_ID,
          COUNT(B_ID) AS CNT_B,
          MAX(PREZZO_ATTUALE) AS MAX_PRICE,
          SUM(TOT_PUNT_AB) AS SUM_AB,
          SUM(TOT_CAMBI_AB) AS SUM_C_AB,
          (CNT_B-SUM_AB) AS UNIQUE_B,
          (0.035*3*CNT_B+MAX_PRICE) AS COSTI,
          45-SUM(CONT_PROVV_AB) AS COUNTDOWN
   FROM MONITOR
   WHERE AUCT_ID = '123456789'
   GROUP BY B_ID) AS B
  ON A.B_ID= B.B_ID
WHERE A.AUCT_ID = '123456789'
ORDER BY B.MAX_PRICE DESC,
         B.CNT_B DESC

shows

ERROR: [MySQL][ODBC 3.51 Driver][mysqld-5.5.33-31.1-log]Unknown column 'CNT_B' in 'field list'

If I leave CNT_B, it tells me MAX_PRICE and so on.

I'm sure. It's a INNER JOIN malediction!

Here the fiddle with the error

SOLUTION

I can't use ALIAS value inside same sub-query.

So the new VALID query is:

   SELECT A.B_ID,
           B.CNT_B,
           B.MAX_PRICE,
           B.SUM_AB,
           B.SUM_C_AB,
           B.UNIQUE_B,
           B.Costi,
           B.COUNTDOWN,
           A.AUCT_ID
    FROM MONITOR AS A
    INNER JOIN
      (SELECT B_ID,
              COUNT(B_ID) AS CNT_B,
              MAX(PREZZO_ATTUALE) AS MAX_PRICE,
              SUM(TOT_PUNT_AB) AS SUM_AB,
              SUM(TOT_CAMBI_AB) AS SUM_C_AB,
              (COUNT(B_ID)-SUM(TOT_PUNT_AB)) AS UNIQUE_B,
              (0.035*3*COUNT(B_ID)+MAX(PREZZO_ATTUALE)) AS COSTI,
              45-SUM(CONT_PROVV_AB) AS COUNTDOWN
       FROM MONITOR
       WHERE AUCT_ID = '123456789'
       GROUP BY B_ID) AS B
      ON A.B_ID= B.B_ID
    WHERE A.AUCT_ID = '123456789'
    ORDER BY B.MAX_PRICE DESC,
             B.CNT_B DESC

and the SQL FIDDLE

Thanks

Oscar

Results from a `SHOW CREATE TABLE MONITOR` would be nice, but I notice that your query references a `C.MAX_PRICE` when no `C` table is specified, so that should be fixed. Doh, sniped. — zanlok, Nov 02 '13 at 05:41
I edited post with correction and the SQLfiddle for the error example — Oscar Zarrus, Nov 02 '13 at 15:43

score 1 · Accepted Answer · answered Nov 04 '13 at 01:40

Move up from Sub-query to the main the following:

 (CNT_B-SUM_AB) AS UNIQUE_B,
 (0.035*3*CNT_B+MAX_PRICE) AS COSTI,

Because you are trying to call an Alias from a Computed Aggregate function by itself.

So that you will have this:

SELECT A.B_ID,
   B.CNT_B,
   B.MAX_PRICE,
   B.SUM_AB,
   B.SUM_C_AB,
   B.CNT_B-B.SUM_AB AS UNIQUE_B,
   (0.035*3*B.CNT_B+B.MAX_PRICE) AS COSTI,
   B.COUNTDOWN,
   A.AUCT_ID
FROM MONITOR A
INNER JOIN
 (SELECT B_ID,
      COUNT(B_ID) AS CNT_B,
      MAX(PREZZO_ATTUALE) AS MAX_PRICE,
      SUM(TOT_PUNT_AB) AS SUM_AB,
      SUM(TOT_CAMBI_AB) AS SUM_C_AB,
      45-SUM(CONT_PROVV_AB) AS COUNTDOWN
FROM MONITOR
WHERE AUCT_ID = '3453015'
GROUP BY B_ID) B
ON A.B_ID= B.B_ID
WHERE A.AUCT_ID = '3453015'
ORDER BY B.MAX_PRICE DESC,
     B.CNT_B DESC

See SQLFiddle

score 0 · Answer 2 · answered Nov 02 '13 at 05:53

0

Can you try this, which doesn't reference a C table:

SELECT A.B_ID,
       B.CNT_B,
       B.MAX_PRICE,
       B.SUM_AB,
       B.SUM_C_AB,
       B.UNIQUE_B,
       B.Costi,
       B.COUNTDOWN,
       A.AUCT_ID
FROM MONITOR AS A
INNER JOIN
  (SELECT B_ID,
          COUNT(B_ID) AS CNT_B,
          MAX(PREZZO_ATTUALE) AS MAX_PRICE,
          SUM(TOT_PUNT_AB) AS SUM_AB,
          SUM(TOT_CAMBI_AB) AS SUM_C_AB,
          (CNT_B-SUM_AB) AS UNIQUE_B,
          (0.035*3*CNT_B+MAX_PRICE) AS COSTI,
          45-SUM(CONT_PROVV_AB) AS COUNTDOWN
   FROM MONITOR
   WHERE AUCT_ID = '123456789'
   GROUP BY B_ID) AS B
  ON A.B_ID= B.B_ID
WHERE A.AUCT_ID = '123456789'
ORDER BY B.MAX_PRICE DESC,
         B.CNT_B DESC

answered Nov 02 '13 at 05:53

zanlok

1,608
1
16
29

It's the same. I made and SqlFiddle. – Oscar Zarrus Nov 02 '13 at 15:44
Not sure what you mean by "it's the same". Is there still an error? That fiddle isn't showing an error with my suggested change. – zanlok Nov 04 '13 at 20:51
Sorry zanlok, but if I copy/paste your query it gives the same error. Probably I'm missing something. Anyway, I solved by making tests for each subquery. If you look here http://sqlfiddle.com/#!2/f10e3/1 you can see the same error that gave me, that I solved it avoiding the use of the values of aliases in the same subquery. In fact if you see your query, for example the "(0.035*3*CNT_B+MAX_PRICE) AS COSTI" [istead of "(0.035*3*COUNT(B_ID)+MAX(PREZZO_ATTUALE) AS COSTI"] tries to use the value of CNT_B and MAX_PRICE made in the same query. And this "it's the same" of my first query. – Oscar Zarrus Nov 05 '13 at 03:01

MySQL: Join table iteself. Unknow column

2 Answers2