Link to home
Start Free TrialLog in
Avatar of liljegren
liljegrenFlag for Sweden

asked on

SQL subqueries

Now, I want to learn how to write a SQL subquery. In a standard module I have lots of functions to return SQL strings to make recordsets under different circumstances. Sometimes I want to delete some of the records after calling one of the functions. First I make a recordset wich is always correct:

Set RS_Search = DB_Search.OpenRecordset(SQL)

Here's the SQL string:

"SELECT * FROM " & Table & " ORDER BY [Year], [Num];"

And now, if AUTHORITY is 1 I want to extract the records that doesn't have Status = F. Something like this, I thought:

NewSQL = "SELECT * FROM " & Table & " WHERE [Status] <> 'F' IN (" & SQL & ");"
Set RS_Search = DB_Search.OpenRecordset(NewSQL)

Unfortunately it doesn't work. How can I make it work without having to write the second query in all the functions?
Avatar of guillems
guillems

The right sentence I Think is:

dim sSQL as string
dim NewSQL as string
sSQL = "SELECT STATUS FROM " & Table
NewSQL = "SELECT * FROM " & Table & " WHERE [Status] <> 'F' IN (" & sSQL & ");"
set RS_Search = DB_Search.Openrecordset(NewSQL)

In any sub-query you must select only the field that you do the WHERE.

I hope this help you

Avatar of liljegren

ASKER

Sorry, but this is not really what I asked for. I want to make a new query with the result of an existing query. The module with all the collected SQL strings may not be rewritten. I need a way to use the results of a query (i.e. a recordset) in another query(to filtrate the recordset once again under special circumstances). I thought subqueries would do this.

liljegren,

I think I understand what you are try to do.  However, I am not sure you can query a recordset with SQL.

I suggest you put your first query results into a temp. table, and then you query the temp table, When you finished, delete the temp table.

If You think this can work for you, and you want me to show you the code, let me know. Meanwhile, If I see anything easier that suit you, I will get back to you.

Good luck and best wishes in your project.
Dalin


I think the first problem is that you are trying to use the wrong keyword.  The IN keyword specifies a table that is external to the JET database.

Another part of the problem is that the example that you give is ill-suited for a subquery.  Specifically,your query can be written as such:

SELECT * FROM Table WHERE Status <> 'F' ORDER BY Year, Num

However, since you want to learn about subqueries, allow me to offer up this scenario:

Let's say that you have a table of products named Catalog wherein you have the name, product code, and cost of each product.  If you wanted to produce a list of products (name & code) whose cost exceeded the average cost of all of the products, you would write it as such:

SELECT Name, ProductCode FROM Catalog WHERE Cost > (SELECT AVG(Cost) FROM Catalog)

A subquery can only return a single value and should be used in a comparison clause such as WHERE or HAVING.

This would allow you achieve your goal of combining the two queries into a single operation.  

Hope this helps!
zsi,
I think liljegren wants to have a results of the first query, and then make additional queries (may not be one) based on some other conditions which may be related to the results of the first query. So it is not suited for the sub-query we are talking about, which excute the first query every time. When the database is small, that is OK, but if the database is huge, it is more desirable to put the first query results somewhere, and only go through the small set, especally if the 2nd query is repeated over and over again.
Regards
Dalin
Thank you for this aspiring answer Zsi, but as Dalin said, this is not really what I want. I'm sorry that I used the title "SQL subqueries" for this question, it was misleading.

Thank you again Dalin. I tried your solution, but I think I do domething wrong. Here's my code:

' SQL is the pre-defined SQL string
Set RS_Search = DB_Search.OpenRecordset(SQL)

' Filter the recordset once again, to hide certain records
' from certain users.
If AUTHORITY = 1 Then
    ' Make a copy of the RS_Search recordset
    Set RS_Temp = RS_Search.OpenRecordset()
    ' Filter the temporary recordset
    RS_Temp.Filter = "Status <> 'F'"
    ' Copy the records back to the original recordset
    Set RS_Search = RS_Temp.OpenRecordset()
    ' Close the temporary recordset
    RS_Temp.Close
End If

I couldn't find any other way to do this, so I used the built in .Filter method in VB. But it doesn't do anything. The number of records is the same before and after the filtering.

I believe the filter method is not available to the recordset object.

I need some info, and then maybe I can help you.  First, do you need the data to be dynamic?(ie you can write back to it), or are you just trying to pull information for people.  If you are attempting to do this dynamically, then I am not sure how you could save time by excersizing this method over zsi's.  If you are specifying criteria in the original SQL statement, there are some things you could do to speed things up, but as the case stands, you are pulling in the entire database, and only ordering it(also time consuming), and then attempting to query.  Possibly you can create a tabledef if the info is from an outside source, but I don't think that will save any time.

If it is okay to do this statically, you may want to consider using code to create a table from the data you need to pull in.
(like DoCmd.RunSQL "SELECT * INTO [temptable] FROM " & Table & " ORDER BY [Year], [Num];" )  Base all your queries on this new table, and then delete the table(DoCmd.DeleteObject acTable, "temptable") when you are done.(This will save some time, but will use up hard drive space while it is running.)

I hope this is at least helpful.  I might be able to get you some better answers as the intent becomes more clear
ASKER CERTIFIED SOLUTION
Avatar of Dalin
Dalin

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry if I gave incorrect info.  I will research better before posting next time.