SQL sub-queries

[erkka]

I first make a SQL query from a table
and then I'd like to perform several sub-queries from the result of the first query.

What is the correct way to do this in Lazarus?

I have

ODBCConnection1 - pointing to my database
SQLTransaction1 - associated with that
SQLQuery1, where
  .Database:=ODBCConnection1
  .Transaction:=SQLTransaction1
Datasource1 - pointing to SQLQuery1, for displaying data


But I can't simply make a SQLQuery2, where
 .Database:=SQLQuery1 ?

Then I tried to set SQLQuery2
 .Database:=ODBCConnection1
 .Datasource:=Datasource1
but it seems to draw data from the whole table, instead of performing an sub-query from results of query1.

With my skills at this level, I could just make the SQL SELECT statements of the query1 bit more complex to get the results I need - but if there is a way to make subqueries, please tell me.
(I tried to check some deplhi-books and sql-tutorials over the net, but couldn't locate anything spesific to my needs.)

[JD]

I've never tried it your way but what I normally do is to nest the sql queries.

Assume I have a table with all the countries of the world. From this table I want to extract all European countries with names beginning with "B". The nested SQL query below works perfectly.

Code: [Select]

select name from (select * from countries where continent = 'Europe') where name like 'B%'

The inner sql query will return ALL the European countries but the final result dataset will be restricted to just Belgium, Belarus & Bosnia.

It is this nested query that I pass as a parameter to a SINGLE query component in my code. I don't need to assign one datasource to another one.

Cheers,

 

[erkka]

Your example sure will work, thanks.

But suppose I'd like to have all the european countries beginning with B, and then all the european countries beginning with F and then with M.

Somehow it would feel more intuitive and effective to make it like this:

1. search all the countries in the world and pick the european ones
2. search the results of 1. and pick those beginning with B
3. search the results of 1. and pick those beginning with F
4. search the results of 1. and pick those beginning with M

[Lord_ZealoN]

Your example sure will work, thanks.

But suppose I'd like to have all the european countries beginning with B, and then all the european countries beginning with F and then with M.

Somehow it would feel more intuitive and effective to make it like this:

1. search all the countries in the world and pick the european ones
2. search the results of 1. and pick those beginning with B
3. search the results of 1. and pick those beginning with F
4. search the results of 1. and pick those beginning with M

Easy, you have to pass the letter by param.

Probably I don't understood your question?

[JD]

Your example sure will work, thanks.

But suppose I'd like to have all the european countries beginning with B, and then all the european countries beginning with F and then with M.

Somehow it would feel more intuitive and effective to make it like this:

1. search all the countries in the world and pick the european ones
2. search the results of 1. and pick those beginning with B
3. search the results of 1. and pick those beginning with F
4. search the results of 1. and pick those beginning with M

Hi erkka,

I was actually expecting a response like you gave above. What I would do is have one DBGrid on a form containing the results of your query 1. above. I would then place a combobox with all the letters of the alphabet & the string 'Default' on the form. The 'Default' option is all European countries and its query string parameter is

Code: [Select]

strQuery := 'select * from countries where continent = QuotedStr('Europe')';

 but when the user selects a letter from the combobox, the OnChange event of the combobox is fired and the string below is passed as a parameter to the Query component

Code: [Select]

strNestQuery := 'select name from (select * from countries where continent = QuotedStr('Europe')) where name like '  +  '''  +  cboLetter.Caption  + '%''';

All the apostrophes above are single apostrophes. The combobox name is cboLetter.

The DBGrid is cleared everytime the combobox caption is changed & the query above is executed. It works for all 26 letters of the alphabet!

The entire process of clearing the DBGrid & requerying the database & then refilling the DBGrid is actually very fast.

I usually give the user the option to save the DBGrid to a spreadsheet & for this I use the FpSpreadsheet package.

I hope that helps.

Cheers,

 

[eny]

KISS; SQL was invented for a reason.
Stay away from nested queries as much as possible.
Worst case they are exponentially slower than non-nested ones.

And in this case...

Code: [Select]

select name from (select * from countries where continent = 'Europe') where name like 'B%'


It's much more efficient to simplify it to a single query:

Code: [Select]

select name
  from countries 
 where continent = 'Europe'
   and name like 'B%'

Choose your indexes wisely and let the rdbms do the hard work.

[JD]

KISS; SQL was invented for a reason.
Stay away from nested queries as much as possible.
Worst case they are exponentially slower than non-nested ones.

It's much more efficient to simplify it to a single query:

Code: [Select]

select name
  from countries 
 where continent = 'Europe'
   and name like 'B%'

Choose your indexes wisely and let the rdbms do the hard work.

Elementary my dear eny. Everybody knows that nested queries can be replaced by 'AND' clauses. 

As for nested queries being "exponentially" slower than non-nested queries, I think the use of the word "exponential" is highly exaggerated. 

The whole idea behind this was to demonstrate a way of extracting data from the results of another dataset. It was not meant to be an example of efficiency & speed. If that was the case, I would have advised the use of stored procedures.

Cheers,

JD

[eny]

As for nested queries being "exponentially" slower than non-nested queries, I think the use of the word "exponential" is highly exaggerated.

I didn't say nested queries were exponentially slower  
And no, in the worst case it's nog 'highly exaggerated'. Even a system like Oracle will choke  surprisingly quickly when used incorrectly of inefficiently.

In the examples given the nesting is probably not that big of an issue.
But it's also not necessary.

[JD]

Stay away from nested queries as much as possible.
Worst case they are exponentially slower than non-nested ones.

You did say they are "exponentially" slower & I still think it is exaggerated, worst case or not . That implies speeds that beggars belief; like comparing a Ferrari to a bicycle.

If what you meant is that in some databases e.g Oracle, poorly designed nested queries are slow then I would agree with you because you cited a special case. I know of other databases where they run fast.

I know of case studies where database administrators argue for & against the use of nested queries. Programmers & database administrators are like economists, there is no universally accepted consensus among them  . In my opinion, there's always another alternative & other ideas are welcome. The more the merrier  

Anyway thanks for your comments.

Cheers  

[erkka]

OK, I use bunch of ANDs and ORs in my SELECT-clause. Just because I like that style 

And my original question was about code economy and efficiency - as I like that style too 

I know it works like this

1. SELECT name FROM countries WHERE continent=europe AND name like 'B%'

2. SELECT name FROM countries WHERE continent=europe AND name like 'F%'

3. SELECT name FROM countries WHERE continent=europe AND name like 'M%'


but to me it seems that it would be more efficient to have it like:

1. SELECT name FROM countries WHERE continent=europe

2. SELECT name FROM result-of-1. WHERE name like 'B%'

3. SELECT name FROM result-of-1. WHERE name like 'F%'

4. SELECT name FROM result-of-1. WHERE name like 'M%'


I don't know if the difference in speed/efficiency is that of bicycle and ferrari, but still - I'm working with invoice data, containing thousands of lines. And I'd like to first search invoices with dates between 1.1.2009 - 31.12.2009 - giving me some one thousand lines only. And then perform more detailed searches from that. 

[JD]

OK, I use bunch of ANDs and ORs in my SELECT-clause. Just because I like that style 

And my original question was about code economy and efficiency - as I like that style too 

I know it works like this

1. SELECT name FROM countries WHERE continent=europe AND name like 'B%'

2. SELECT name FROM countries WHERE continent=europe AND name like 'F%'

3. SELECT name FROM countries WHERE continent=europe AND name like 'M%'


but to me it seems that it would be more efficient to have it like:

1. SELECT name FROM countries WHERE continent=europe

2. SELECT name FROM result-of-1. WHERE name like 'B%'

3. SELECT name FROM result-of-1. WHERE name like 'F%'

4. SELECT name FROM result-of-1. WHERE name like 'M%'


I don't know if the difference in speed/efficiency is that of bicycle and ferrari, but still - I'm working with invoice data, containing thousands of lines. And I'd like to first search invoices with dates between 1.1.2009 - 31.12.2009 - giving me some one thousand lines only. And then perform more detailed searches from that. 

I use stored procedures to do what you are describing above where the input to one stored procedure is the output of another one. It is very fast & efficient because it is server-side and it does certain things not possible with run of the mill queries. So it is very possible to do exactly what you want but it may depend on if your database permits it.

What database are you using?

[erkka]

What database are you using?

I'm using MS-ACCESS databases, through ODBC-connection. And forced to do so... My friend runs a company, and the invoice-management program they use stores data in access-database. I aim to write them an automated reporting tool, which would perform different queries from the data, analyse and organize it and print it out. So nothing very dramatical, just a good piece of practice for me 

[eny]

Something else to consider: if you execute a query, the result comes to you (your application) 'via the network'.
The smaller the resultset, the less information has to go via the network and the quicker you have your response i.e. your report.

[erkka]

so what am I missing then? I thought that is exactly the reason why I'd like to perform my queries in row.

Of course I could just drop the idea of using several SQL-queries and make it like this:

1. with SQL, SELECT code,total,VAT FROM invoices WHERE date BETWEEN A and B

2. Insead of SQL, write a piece of code which browses through results of 1 and calculates sum of (total+VAT) of all the records where code='X'

3. run that same piece of code again, now calculating the sum of (total+VAT) of all the records where code='Y'

4. once again, where code ='Z'

well, the example is over-simplified, but I hope you get the idea  

ps. luckily enough the database-files are located on the very same computer as the invoicing program and my own piece of code, so it doesn't have to travel that much accross network. but still, I'd like to keep my code tidy, clean & efficient.

[JD]

What database are you using?

I'm using MS-ACCESS databases, through ODBC-connection.

I'm afraid I no longer use MS Access. I've migrated my work to SQLite, Firebird & PostgreSQL. See the page below for an example of what is possible in Access 2000 (the last version I used was Access 2002).

Creating and Modifying Stored Queries in Microsoft Access Databases with ADOX
http://msdn.microsoft.com/en-us/library/aa140021%28office.10%29.aspx

Google is your friend here. 

You can also use T-SQL but that may be an overkill for your situation.

Like eny advised, be careful with the size of your resultset. Don't extract too much from the database at one time. On a desktop application you might get away with it but the day you migrate the application to client-server or N-tier, you'll run into problems. I personally prefer paged resultsets where results are delivered to the user in pages of say 20 rows in a grid at a time. The user then navigates through the pages looking for the desired record. Web searches use the same principle.

Hope that helps.

Cheers,

JD