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:
x
+-----------+----------+-------------+--------------+------+----------+--------------+
| 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.
Advertisement
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.