CODEWITHSUNDEEP
sqlmysql-error-1146
pharask
pharask

Reputation: 332

MySQL error code 1146 in MySQL Workbench action output

I have just started learning SQL. When I wrote this code 

create database tutorial_1;
use tutorial_1;
create table tutorial
(
id int primary key,
name varchar(40),
duration decimal(4,2),
date date
);
insert into tutorial(id, name, duration, date)
values(1, 'c', 23.12, '2014-02-10');
insert into tutorial
values(2, 'python', 12.45, '2014-02-11');
insert into tutorial
values(3, 'sql', 34.17, '2014-02-14');

create database tutorial_2;
use tutorial_2;
create table tutorial_info
(
tutorial_id int primary key,
views int(40),
likes int(40),
dislikes int(40),
shares int(40)
);
insert into tutorial_info(tutorial_id, views, likes, dislikes, shares)
values (1, 23411, 124, 12, 19);
insert into tutorial_info(tutorial_id, views, likes, dislikes, shares)
values (2, 29019, 920, 100, 234);
insert into tutorial_info
values (3, 9019, 129, 23, 8);
insert into tutorial_info

SELECT t.id, t.name,
ti.views, ti.likes, ti.dislikes, ti.shares
FROM tutorial AS t
JOIN tutorial_info AS ti
ON t.id=ti.tutorial_id;

on SQL file, action output shows:

Error Code: 1146. Table 'tutorial_2.tutorial' doesn't exist 0.000 sec

but when I checked the table on SCHEMAS, I found that table. What do I do to fix it?

Upvotes: 0

Views: 4888

Answers (2)

Lajos Arpad
Lajos Arpad

Reputation: 76543

You are creating two separate databases: tutorial_1 and tutorial_2.

Then, you switch to the tutorial_2 database. After that, you are selecting a join between tutorial and tutorial_info. tutorial is in the tutorial_1 database, while tutorial_info is in tutorial_2 database. Since you are in tutorial_2 database, MySQL tries to load records from the tutorial table, which does not exist in the tutorial_2 database, since it was created into the tutorial_1 database.

EDIT: I have conducted a few experiments and I believe you can do a cross-database join like this:

SELECT t.id, t.name,
ti.views, ti.likes, ti.dislikes, ti.shares
FROM tutorial_1.tutorial AS t
JOIN tutorial_info AS ti
ON t.id=ti.tutorial_id;

Upvotes: 2

Renzo
Renzo

Reputation: 27424

tutorial is a table of database tutorial_1, while you are working in the scope of database tutorial2 (after use tutorial_2). So you should write:

SELECT t.id, t.name,
ti.views, ti.likes, ti.dislikes, ti.shares
FROM tutorial_1.tutorial AS t
JOIN tutorial_info AS ti
ON t.id=ti.tutorial_id;

In general you should work only a single database.

Upvotes: 1

Related Questions