Nested SELECT's in Access and MSSQL problems.

[tcollins]

Does anyone know why the following code doesn't run in Access?

SELECT a FROM (SELECT a FROM b WHERE c=1);

I know it doesn't make a lot of sense, but its the basis for what I have to do.I have one SELECT statement producing the results that I need, however, I now need to find a mins and maxs of all my datapoints in the columns reported back by the first query.We have it in two seperate queries right now, but we need to compile them into one. Nested SELECTS don't seem to work the way one would expect.My results always are:

"Syntax error in FROM clause."

 

[MikeLacey]

would be more usual to use

select a from b where c in (select c from d where e=1) [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br> [/sig]

 

[Guest_imported]

Thats the problem. The results from our nested SELECT do not represent a condition. It is actual data we need to process further. Its like two aggreated functions...you can't do a:
max(max(col1)-min(col3))

We need to find the maximum and minmum differences between three different columns of values. The data we get back on the inner is what we're looking for, but we need to find the mins and maxes now for each column. Thats our problem {grin}.

 

[MikeLacey]

You *can* do something like this in Oracle
[tt]
Select A_Table.A, B_Table.B, C_Table.C
From
(Select max(A) A From A_Table) A_Table,
(Select max(B) B From B_Table) B_Table,
(Select max(C) C From C_Table) C_Table
Where .....
[/tt]
[sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br> [/sig]

 

[bigscouse]

It's been a few months since I have done any SQL and I have no where to test it, however this might be worth a stab: -

SELECT tablea.col1, tableb.col3 rescol as (tablea.col1 - tableb.col3)
FROM atable tablea, atable tableb
WHERE tablea.col1 = (
SELECT max(tablec.col1)
FROM atable tablec
WHERE tablec.col2=1 )
AND tableb.col3 = (
SELECT min(tabled.col3)
FROM atable tabled
WHERE tablec.col2=1); [sig]<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>Top man[/sig]

 

[MalcolmW]

The syntax for a derived table in SQL Server is
SELECT T1.a, T1.b
FROM
(SELECT T2.a, T2.b FROM AnyTable AS T2 WHERE T2.c=1) AS T1
You don't need all the explicit table references, but they may help clarify how the derived table works.


[sig][/sig]

 

[karluk]

Yes, Malcolm is right. There appear to be some serious errors in the &quot;minus&quot; operator in SQL Server. Better avoid it until they get the bugs out. I did three different queries and they all returned unexpected results:

select * from mytable
minus
select * from mytable
appears to do two executions of &quot;select * from mytable&quot;.

select * from mytable
minus
select * from mytable
where id# > 100
appears to execute the second select and ignore the first.

select * from mytable
where id# > 100
minus
select * from mytable
generates a syntax error.

[sig][/sig]

 

[karluk]

Please ignore my previous post. I inadvertently posted it as a reply to this thread, instead of the thread I was trying to answer. [sig][/sig]

 

[tcollins]

Thank you all for replying. Our final solution ending up being as simple as:

SELECT t1.a from (SELECT a FROM b WHERE c=d) t1

...or something similar. The nested SELECT doesn't return a list of 'conditions' back to us. It returns back processed data that we must further process. MSSQL does this well, MS Access doesn't. MS Access does our crosstab queries well, MSSQL doesn't (it seems a pain and dont have it working yet). What to do. If quering for data was all we needed, things would be smooth, however we need to query, process, and format all in one step. But we are further, thank you again. [sig][/sig]