SQL Query giving an error of Unknown Column

Question

I am creating an application in PHP of Power Meter Analysis. I have following table structure:

table: 'feeds'

| feed_id | device_no | current1 | voltage1 | power_factor_1 | vc1 | ic1 | date_added
-------------------------------------------------------------------------------------
| 36752   | 2         | 36.048   | 196.01   | 0.9            | 1   | 1   | 2014-06-23 14:14:44
| 36753   | 2         | 35.963   | 195.59   | 0.9            | 1   | 1   | 2014-06-23 14:15:34

and so on.

table: 'machine'

| machine_id | machine_phone | machine_name | company_id |
----------------------------------------------------------
| 1          | 2             | ABC Machine  | 1          |
| 2          | 093           | DEF Machine  | 1          |

I need records on hourly basis and I have written the following query for this purpose:

$sql =  "
            SELECT
                SUM(t.power1) AS 'power1'
                , HOUR(t.date) AS 'pulse_hour' 

            FROM    (
                SELECT
                    IF(@diff = 0, 0, (((f.voltage1*f.vc1)*(f.current1*f.ic1)*(f.power_factor_1))/1000) * (@diff/3600)) AS 'power1'
                    , IF(@diff = 0,0, TIME_TO_SEC(f.date_added) - @diff) AS 'deltaT'
                    , @diff := TIME_TO_SEC(f.date_added)
                    , f.date_added AS 'date'
                FROM
                    feeds f,
                    (SELECT @diff := 0) AS X
                left join
                    machine m
                on
                    f.device_no = m.machine_phone
                left join
                    company c
                on
                    c.company_id = m.company_id
            ";

    $sql .= $params['machine_id'] ? " where f.device_no = '".$params['machine_id']."'" : " where f.device_no > 0";
    $sql .= $params['machine_pulse_datetime_from'] ? " and f.date_added >= '".$params['machine_pulse_datetime_from']."'" : "";
    $sql .= $params['machine_pulse_datetime_to'] ? " and f.date_added <= '".$params['machine_pulse_datetime_to']."'" : "";
    $sql .= $params['company_id'] ? " and c.company_id = '".$params['company_id']."'" : "";

    $sql .= "
                ORDER BY
                    f.date_added ASC
                ) t
            GROUP BY HOUR(t.date)
            ORDER BY HOUR(t.date) ASC

            ";

The query is running OK if I remove the following part from the query:

left join
                    machine m
                on
                    f.device_no = m.machine_phone
                left join
                    company c
                on
                    c.company_id = m.company_id

But with this part It is giving me following error:

Error Code : 1054
Unknown column 'f.device_no' in 'on clause'

can you please help me to sort this out... I have spent an hour with this query :(

You have to join with feeds table which is missing that's why you getting error — Sadikhasan
– Sadikhasan, Commented Jun 23, 2014 at 12:10
I can't see a cause of the issue as you do seem to have the feeds table in there. However you are using a mix of implicit and explicit joins which is a bit confusing; I would suggest explicitly joining the X sub query (ie, use CROSS JOIN rather than just a comma). Also you appear to be calculating the difference between records, but the select (and thus the calculation) is effectively done before the ORDER BY f.date_added ASC clause, hence you results might not be as expected. — Kickstart
– Kickstart, Commented Jun 23, 2014 at 12:13

Gordon Linoff · Accepted Answer · 2014-06-23 12:16:46Z

3

This is your join:

         FROM feeds f,
              (SELECT @diff := 0) AS X left join
              machine m
              on f.device_no = m.machine_phone left join
              company c
              on c.company_id = m.company_id

The problem is that you are mixing explicit and implicit joins. You can fix this by replacing the comma with cross join:

        FROM feeds f cross join
              (SELECT @diff := 0) AS X left join
              machine m
              on f.device_no = m.machine_phone left join
              company c
              on c.company_id = m.company_id

The issue, which is buried deep in the documentation for select, is that , is a lot like a cross join with the exception of scoping rules -- that is, when the table aliases are recognized. With a comma, the table aliases are not recognized as you expect. They are with a cross join.

Here is the reference:

INNER [CROSS] JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

answered Jun 23, 2014 at 12:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kickstart Over a year ago

I just set up and SQL fiddle for this and switching a CROSS JOIN fixes the problem - sqlfiddle.com/#!2/7a52ae/1

Collectives™ on Stack Overflow

SQL Query giving an error of Unknown Column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related