Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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!
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
open your MYSQLWorkbench
write this USE databaseName, in your example it becomes like that
USE myDB.table_a then enter,
then start writing normal queries
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:-
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.