Questions around some SQL queries

Save money by purchasing our NeoPlugins bundles. Five, Ten and even the complete NeoPlugins collection at an incredible price!

Take a look at our YouTube channel and subscribe to be notified about new videos!

DecSoft HTML Compiler include a convenient Command Line Compiler, so you can compile your apps from the console.

DecSoft App Builder include a convenient Command Line Compiler, so you can compile your apps from the console.

Create apps and expand their capabilities by using the world's popular programming language: Javascript.

DecSoft NeoPlugins put your VisualNeo projects into another level with +1,000 new actions for your apps.

Create modern HTML apps and games at another level making things impossible to do even by modern HTML apps and games.

DecSoft HTML Compiler is easy to use: set your project's options and compile your apps and games in a nutshell.

Compile, convert your modern HTML apps and games into Microsoft Windows executable programs without dependencies.

Create multiplatform apps with DecSoft App Builder, including Android and iOS with the same base of code.

DecSoft NeoPlugins are available in both runtime and designtime flavours to keep your apps as small as possible.

Apache Cordova's official plugins are supported out of the box and there are hundreds of plugins ready to use.

Design for small screen devices but deploy also (without changes) in larger devices like tablets.

DecSoft App Builder integrates an Open AI assistant in order to assist you while developing your apps.

Save time, resources and money creating apps for Android, iOS and more with the same base of code.

Take a look at some frequently asked questions about us and our products!

Amin Mousavi

Hi David, I have got stock in optimising my query somewhere I think you could advise. I am trying to run the below query on the API you have provided for AB Database example:

CREATE VIEW temp AS (SELECT * FROM tb1);
SELECT * FROM temp AS t1 JOIN temp AS t2 ON t1.id = t2.id;

I use Database::getResults in the API to run the SELECT part that works fine, but I had not been able to run the CREATE VIEW as of yet. I tried

Database::beginTransaction();
Database::exec($query)

and a few other pdo functions to execute the CREATE VIEW but had not luck. I appreciate if you advise. Regards,

DecSoft

Hello Amin,

If I am not wrong, to create the VIEW you must use the "Database::exec()" method. So the only thing that I see rare is that if you use "Database::beginTransaction"()", after execute the query, you must execute "Database::commit()". Maybe you no need to use the "begin transaction" at all?

Can this help you in some manner?

Purchase one or more licenses of our products if they are useful to you!

Amin Mousavi

Hi David, During the debugging I figured that

Database::beginTransaction();
Database::exec($query)

Was infact working correctly, and my view was created, the reason I get the errors is that I am trying to replicate "WITH" in MySQL and still have no luck. Thanks a million

DecSoft

Hello Amin,

Then I am not sure if can understand. First of all, probably you want to create the view over and over again: once you create the view (for example with a database manager like PhpMyAdmin), what you must to do is to execute the appropriate SQL query. You talk about errors, Amin, but can you please refer that errors here?

Purchase one or more licenses of our products if they are useful to you!

Amin Mousavi

Thanks a lot David for always going the extra mile, I did not expect you to look into that because it is totally irrelevant to AB and the APIs I got from it. But any how to clear things up. I am running a query like below:

SELECT A,B FROM (SELECT * FROM T1) AS Q1 JOIN SELECT B,C FROM (SELECT * FROM T1) AS Q2 ON Q1.B=Q2.B;

Because both sides of JOIN are running the same query (SELECT * FROM T1) it will be more efficient if we can buffer it somewhere first time it is run and then reuse it the second time. This in a simplified example and when the reused query is something with a few joins and subqueries and data has millions of records it will make a measurable difference. In SQLSERVER and ORACLE this issue can be taking care of by TEMPORARY tables and/or WITH but MySQL temporary tables does not help with this. WITH is available in MySQL 8 apparently but my server's SQL version is 5.6 and does not support WITH so I am stuck doing my best to replicate these using views, memory engines and technically anything that has the ability to speed up my query in a similar fashion. Don't worry about it though, I will find a way for it ;)

DecSoft

Hello Amin,

I am not an expert in SQL, so, I am not sure if can help you in the best way, but at least I can always try it. :-)

Purchase one or more licenses of our products if they are useful to you!

Everybody can read the DecSoft support forum for learning purposes, however only DecSoft customers can post new threads. Purchase one or more licenses of some DecSoft products in order to give this and other benefits.