SubSelect in A97

[Kahuna]

Hi Folks

I need to generate a fairly long SQL string to run as an action query - I do
this in code.

General select is simple enough, but using several tables with search
criteria in each is tripping me up. I've found it difficult to determine the
numbers of parentheses etc, so I think using a SubSelect will be a safer
solution, where I can generate each Select statement seperately and
concatenate them before running.

My challenge is getting an example of a SQL string with several SubSelects
to use as an example. Not sure if the Sub should be in the FROM section or
the WHERE section of the SQL String?

Appreciate any feedback.

 

[Bob Barrows [MVP]]

Hi Folks

I need to generate a fairly long SQL string to run as an action query
- I do this in code.

General select is simple enough, but using several tables with search
criteria in each is tripping me up. I've found it difficult to
determine the numbers of parentheses etc, so I think using a
SubSelect will be a safer solution, where I can generate each Select
statement seperately and concatenate them before running.

My challenge is getting an example of a SQL string with several
SubSelects to use as an example. Not sure if the Sub should be in the
FROM section or the WHERE section of the SQL String?

Appreciate any feedback.

It really depends on the purpose of the subqueries. If they are providing
fields to be used in a Select statement, then they are data sources and need
to be in the FROM clause (or sometimes, in the SELECT clause itself)
If they are being used solely to filter the results from the data sources in
the FROM clause, then they should go in the WHERE clause,

A97 was subquery-in-FROM-clause-challenged. The only way to use a subquery
in a FROM clause was to surround it in brackets and finish with a period and
an alias:

FROM [select ... ]. As q1

You might find it easier to use saved queries, but it really depends on what
you are doing.

 

[Kahuna]

Hi Folks

I need to generate a fairly long SQL string to run as an action query
- I do this in code.

General select is simple enough, but using several tables with search
criteria in each is tripping me up. I've found it difficult to
determine the numbers of parentheses etc, so I think using a
SubSelect will be a safer solution, where I can generate each Select
statement seperately and concatenate them before running.

My challenge is getting an example of a SQL string with several
SubSelects to use as an example. Not sure if the Sub should be in the
FROM section or the WHERE section of the SQL String?

Appreciate any feedback.

It really depends on the purpose of the subqueries. If they are providing
fields to be used in a Select statement, then they are data sources and
need to be in the FROM clause (or sometimes, in the SELECT clause itself)
If they are being used solely to filter the results from the data sources
in the FROM clause, then they should go in the WHERE clause,

A97 was subquery-in-FROM-clause-challenged. The only way to use a subquery
in a FROM clause was to surround it in brackets and finish with a period
and an alias:

FROM [select ... ]. As q1

You might find it easier to use saved queries, but it really depends on
what you are doing.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bob - thanks for that feedback - I was only just begining to realise the
challenges with the A97 FROM clause.

Heres what I'm doing.

I have a form with which a client can enter search criteria (Between (x,y),
Range (1,2,3) etc.

I build an SQL string based on the entries in this form (having parsed the
data of course). Most of thre fields on this form relate to a single table -
(and its the records in that table that are being updated) - but there are
some fields that relate to other tables. And there could be 0 or upto 10
linked tables - dependant upon the search criteria enetered by the user.

Would you have an example of subselects using the WHERE clause Bob?

 

[Bob Barrows [MVP]]

Would you have an example of subselects using the WHERE clause Bob?

Take a look at the other posts I made this morning in the Sub-Query
Problem thread.

 

[Kahuna]

Take a look at the other posts I made this morning in the Sub-Query
Problem thread.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Cant really see a clear-cut example in that thread Bob - any other resources
you could point me at?

 

[Bob Barrows [MVP]]

Cant really see a clear-cut example in that thread Bob - any other
resources you could point me at?

What about (from that thread):

WHERE
mainquery.column-name IN (select single-field from table WHERE ... )

and

WHERE EXISTS
(select * from table t where t.field = mainquery.field)


Don't these qualify as examples that you can adapt to your situation? In
what way do they not serve the purpose?

 

[Kahuna]

What about (from that thread):

WHERE
mainquery.column-name IN (select single-field from table WHERE ... )

and

WHERE EXISTS
(select * from table t where t.field = mainquery.field)


Don't these qualify as examples that you can adapt to your situation? In
what way do they not serve the purpose?

I was looking for an example with multiple subselects and how they are
inserted in the WHERE clause (though I can see how the WHERE EXISTS will be
useful for my purposes).

I can see I'm exasperating you BOB, so thanks for your help anyway!

 

[Bob Barrows [MVP]]

I was looking for an example with multiple subselects and how they are
inserted in the WHERE clause (though I can see how the WHERE EXISTS
will be useful for my purposes).

It would be like any other criteria: use AND or OR as the situation
requires:

WHERE
mainquery.column-name IN (select single-field from table WHERE ... )
OR (
EXISTS
(select * from table t where t.field = mainquery.field)
AND EXISTS
(select * from table2 t where t.field = mainquery.field)
)

I can see I'm exasperating you BOB, so thanks for your help anyway!

That's the wrong word. "puzzling" would be a better word. It's always
risky to try to ascribe emotion to the writer of a plain text message