Complex Select

  • Thread starter Thread starter Jon Bilbao
  • Start date Start date
J

Jon Bilbao

I´m trying a select clause in two steps because it´s too complex.

First:

SELECT Reference, Results.idEnsayo, Results.Num_taladro, min(Results.dTime)
+ 500 AS tIni, max(Results.dTime) - 500 AS tLast
FROM Results INNER JOIN Ensayos ON Results.idEnsayo=Ensayos.idEnsayo
WHERE (Ensayos.Reference=9)
GROUP BY Reference, Results.idEnsayo, Num_Taladro;

This consult fills table called "resTable01" and then

Second (using the previous):

SELECT idEnsayo, Num_taladro, Avg(Dat1) as avgDat1, Avg(Dat2) as avgDat2,
Min(Dat1) as minDat1, Min(Dat2) as minDat2
FROM Results INNER JOIN resTable01 ON (Results.idEnsayo=resTable01.idEnsayo
AND Results.Num_taladro=resTable01.Num_taladro)
WHERE (Results.dTime Between resTable01.tIni and resTable01.tLast)
GROUP BY Results.idEnsayo, Results.Num_Taladro;

Actually the Select is more complex but it shows the problem

I´m trying it using ADO.NET with a DataSet (the database is ACCESS).
I fill it with the first command and i would like to use the table
(DataSet.Tables[0]) to make the new command.
This is my problem, is it possible? There is another way to do that (a
nested select)?.

Thanks in advance
 
Jon said:
I´m trying a select clause in two steps because it´s too complex.

First:

SELECT Reference, Results.idEnsayo, Results.Num_taladro,
min(Results.dTime) + 500 AS tIni, max(Results.dTime) - 500 AS tLast
FROM Results INNER JOIN Ensayos ON Results.idEnsayo=Ensayos.idEnsayo
WHERE (Ensayos.Reference=9)
GROUP BY Reference, Results.idEnsayo, Num_Taladro;

This consult fills table called "resTable01" and then

Second (using the previous):

SELECT idEnsayo, Num_taladro, Avg(Dat1) as avgDat1, Avg(Dat2) as
avgDat2, Min(Dat1) as minDat1, Min(Dat2) as minDat2
FROM Results INNER JOIN resTable01 ON
(Results.idEnsayo=resTable01.idEnsayo AND
Results.Num_taladro=resTable01.Num_taladro) WHERE (Results.dTime
Between resTable01.tIni and resTable01.tLast) GROUP BY
Results.idEnsayo, Results.Num_Taladro;

Actually the Select is more complex but it shows the problem

I´m trying it using ADO.NET with a DataSet (the database is ACCESS).
I fill it with the first command and i would like to use the table
(DataSet.Tables[0]) to make the new command.
This is my problem, is it possible? There is another way to do that (a
nested select)?.

Thanks in advance
Click to expand...

You can replace 'resTable01' in the second query with the first:
SELECT idEnsayo, Num_taladro, Avg(Dat1) as avgDat1, Avg(Dat2) as
avgDat2,
Min(Dat1) as minDat1, Min(Dat2) as minDat2
FROM Results INNER JOIN
(
SELECT Reference, Results.idEnsayo, Results.Num_taladro,
min(Results.dTime)
+ 500 AS tIni, max(Results.dTime) - 500 AS tLast
FROM Results INNER JOIN Ensayos ON Results.idEnsayo=Ensayos.idEnsayo
WHERE (Ensayos.Reference=9)
GROUP BY Reference, Results.idEnsayo, Num_Taladro
) As resTable01
ON (Results.idEnsayo=resTable01.idEnsayo
AND Results.Num_taladro=resTable01.Num_taladro)
WHERE (Results.dTime Between resTable01.tIni and resTable01.tLast)
GROUP BY Results.idEnsayo, Results.Num_Taladro;

(don't know your DB system, so this might not be supported, e.g.
firebird 1.x doesn't support derived tables)

If you don't want to go this route, you can always create the first
query as a DB view and use that in the second, or use a temptable and
use that in the second query.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Back
Top