Converting Nested Queries into Individual Queries

[omario]

I have a lot of trouble reading nested subqueries - I personally prefer to write several mini queries and work from there. I understand that more advanced SQL users find it more efficient to write nested subqueries.

For instance, in the following query:

select distinct b.table_b, a.*
from table_a a
inner join table_c b
on a.id_1 = b.id_1
inner join ( select a.id_1, max(a.var_1) as max_var_1 from table_a a
group by a.id_1) c
on a.id_1 = b.id_1 and a.var_1 = c.max_var_1

I am now trying to convert this big query into individual queries (suppose I establish a connection called "dbhandle"):

#PART 1 :

dbGetQuery(dbhandle, "create table_1 as select distinct b.table_b, a.*
from table_a a
inner join table_c b
on a.id_1 = b.id_1")

#PART 2:

 dbGetQuery(dbhandle, "create table_2 as select a.id_1, max(a.var_1) as max_var_1 from table_a a
group by a.id_1")

#PART 3 (final result: final_table)

dbGetQuery(dbhandle, "create final_table as select a.*, b.*
from table_1 a
inner join table_2 b
on a.id_1 = b.id_1 and a.var_1 = b.max_var_1")

My Question: Can someone please tell me if I have correctly converted the big query into the three mini queries?

Thank you!

[pieterjanvc]

Hi,

It is very difficult to read the query if we don't know the schema of the database (i.e. the tables and the columns they have). Why don't you just compare the output from the "big" query with the result from your 3 small ones? Then you can verify if they are identical.

Also, you are now creating new tables in your database that will take up more space, and depending on the settings it might be needed to drop those tables before you can write them again next time the query is run

PJ