CODEWITHSUNDEEP
mysqlmariadb
Edamame
Edamame

Reputation: 25366

SQL "with" clause - Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS

I have the following query which works fine:

select a.dataId, a.my_no,  b.my_attribute  from myDB.table_a a left join myDB.table_b b 
on a.my_no = b.my_no order by dataId

However, if I include the with clause like below:

with my_table as (
    select a.dataId, a.my_no,  b.my_attribute  from myDB.table_a a left join myDB.table_b b 
    on a.my_no = b.my_no order by dataId
)

select * from my_table

I got the following error:

Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.

This is confusing ... shouldn't these two queries be identical? What did I do wrong here? (I am using MariaDB server) Thanks!

Upvotes: 9

Views: 96279

Answers (6)

Renan Cidale
Renan Cidale

Reputation: 902

That might happen because you have more schemas in your connection. Maybe a database version of the staging/production or other environment. What you can do , is, you right click on the schema that you want to have it as default, and click on Set as Default Schema, now every query that would need the information about a schema, will use the one that you set as default.

I faced the issue when I was trying to run a query

select distinct(status) from inventory_locations;

But then I was getting the following error:

Error Code: 1046. No database selected....

One option would be to do:

select distinct(status) from `schema_name`.inventory_locations;

where the schema_name is the name of the schema that you imported.

Or just do what I said in the beginning. Enjoy

Upvotes: 2

Awara Amini
Awara Amini

Reputation: 572

  1. open your MYSQLWorkbench
  2. write this USE databaseName, in your example it becomes like that USE myDB.table_a then enter,

then start writing normal queries

Upvotes: 0

parth dhulla
parth dhulla

Reputation: 1

create ur own database in MySQL 8.0 Command Line Client - Unicode as create a new database(name) after that use it in your MySQL Workbench

for example : you create a database as parth in (MySQL 8.0 Command Line Client - Unicode)

after that go to workbench and type on the first line USE parth;

and type ur all codes it will work:-

Upvotes: -2

Rick James
Rick James

Reputation: 142298

The difference? Look closely:

select ... myDB.table_a ...

with            table_a ...

Upvotes: 0

Edamame
Edamame

Reputation: 25366

just do:

select * from myDB.my_table

works as well.

Upvotes: 5

Ildar Akhmetov
Ildar Akhmetov

Reputation: 1431

Before a WITH clause, you should specify the database you're using with

USE db_name;

That should solve the problem.

Upvotes: 33

Related Questions