'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!

mysqlmariadb


Solution 1:[1]

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

USE db_name;

That should solve the problem.

Solution 2:[2]

just do:

select * from myDB.my_table

works as well.

Solution 3:[3]

The difference? Look closely:

select ... myDB.table_a ...

with            table_a ...

Solution 4:[4]

  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

Solution 5:[5]

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

Solution 6:[6]

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:-

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ildar Akhmetov
Solution 2 Edamame
Solution 3 Rick James
Solution 4 Simas Joneliunas
Solution 5 Renan Cidale
Solution 6 parth dhulla

Related Questions