ERROR 1054 (42S22): Unknown column ” in ‘where clause’

The MySQL query got following error.

ERROR 1054 (42S22): Unknown column ‘scope.full_name’ in ‘where clause’

Here are two tables as below.

1.scope:

 +-----------+----------+-------------+--------------+------+----------+--------------+
    | handle_id | model_id | define_name | full_name    | name | file     | parent_scope |
    +-----------+----------+-------------+--------------+------+----------+--------------+
    |  15859248 |        0 | tb          | tb           | tb   | 12755864 |            0 |
    |  15859360 |        0 | RCA         | tb.RCA       | RCA  | 12755864 |     15859248 |
    |  15859472 |        0 | FA          | tb.RCA.x1    | x1   | 12755864 |     15859360 |
    |  15859584 |        0 | HA          | tb.RCA.x1.x1 | x1   | 12755864 |     15859472 |
    |  15859696 |        0 | HA          | tb.RCA.x1.x2 | x2   | 12755864 |     15859472 |
    |  15859808 |        0 | FA          | tb.RCA.x2    | x2   | 12755864 |     15859360 |
    |  15859920 |        0 | HA          | tb.RCA.x2.x1 | x1   | 12755864 |     15859808 |
    |  15860032 |        0 | HA          | tb.RCA.x2.x2 | x2   | 12755864 |     15859808 |
    |  15860144 |        0 | FA          | tb.RCA.x3    | x3   | 12755864 |     15859360 |
    |  15860256 |        0 | HA          | tb.RCA.x3.x1 | x1   | 12755864 |     15860144 |
    |  15860368 |        0 | HA          | tb.RCA.x3.x2 | x2   | 12755864 |     15860144 |
    |  15860480 |        0 | FA          | tb.RCA.x4    | x4   | 12755864 |     15859360 |
    |  15860592 |        0 | HA          | tb.RCA.x4.x1 | x1   | 12755864 |     15860480 |
    |  15860704 |        0 | HA          | tb.RCA.x4.x2 | x2   | 12755864 |     15860480 |
    +-----------+----------+-------------+--------------+------+----------+--------------+

2.file:

+-----------+----------+----------+----------+-------------------------------------------------------------------+------------+
| handle_id | model_id | max_time | min_time | name                                                              | scale_unit |
+-----------+----------+----------+----------+-------------------------------------------------------------------+------------+
|  12755864 |        0 |    10000 |        0 | /directory/ART.txt                                               | 1ns        |
+-----------+----------+----------+----------+-------------------------------------------------------------------+------------+

Query command is:

SELECT `name` FROM `file` WHERE `scope`.`full_name`='tb.RCA';

I understand backticks in MySQL means ” allow spaces and other special characters (except for backticks, obviously) in table/column names”, but even the following command causes same error.

 SELECT name FROM file WHERE scope.full_name='tb.RCA';

For more information, MySQL server version is 5.7.20 on Linux system.

Answer

You need to join the tables like this:

SELECT f.name
FROM file f 
JOIN scope s on s.file = f.handle_id
WHERE s.full_name = 'tb.RCA'

The line JOIN scope s on s.file = f.id joins the table data using the column file from the scope table and the handle_id column from the file table.

I have linked this website many times for questions like this – it’s a great visual explanation of joins.

Advertisement

Answer