Clash Royale CLAN TAG#URR8PPP

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

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!

3 Answers
3

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

WITH

USE db_name;


That should solve the problem.

just do:

select * from myDB.my_table


works as well.

The difference? Look closely:

select ... myDB.table_a ...

with table_a ...


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.