How to combine Queries

  • Thread starter Thread starter MostlyH2O
  • Start date Start date
M

MostlyH2O

Hi Folks,

I have a complex query that references a sub-query. It works fine when I
call a sub-query from the main query (using design vew), but when I try to
embed the SQL text of the sub-query into the main query I get a syntax
error...

Here's the main Query. It works fine. It references another query from the
database called "Query1a"

SELECT Benchmarks.CatID, QueryA.*, Query1a.*, QueryA.Benchmarks.BenchID,
Query1a.Benchmarks.BenchID, Benchmarks.BenchID, Benchmarks.Benchmark
FROM (Benchmarks LEFT JOIN [SELECT Benchmarks.*, GroupJobs.*,
GroupJobs.GroupID, Benchmarks.CatID, Benchmarks.BenchID
FROM Benchmarks INNER JOIN GroupJobs ON Benchmarks.BenchID =
GroupJobs.BenchID
WHERE GroupJobs.GroupID=1AND Benchmarks.CatID=40]. AS QueryA ON
Benchmarks.BenchID = QueryA.Benchmarks.BenchID) LEFT JOIN Query1a ON
Benchmarks.BenchID = Query1a.Benchmarks.BenchID
WHERE (((Benchmarks.CatID)=40) AND ((QueryA.Benchmarks.BenchID) Is Not
Null)) OR (((Query1a.Benchmarks.BenchID) Is Not Null));

Here's Query1a...
SELECT AllLatestRecords.*, OrgJobs.*, Benchmarks.CatID, Benchmarks.BenchID
FROM (OrgJobs LEFT JOIN [SELECT Max(SalaryData.Dat) AS MaxOfDat,
SalaryData.BenchID, Benchmarks.CatID, SalaryData.OrgID
FROM Benchmarks INNER JOIN (OrgJobs INNER JOIN SalaryData ON OrgJobs.BenchID
= SalaryData.BenchID) ON Benchmarks.BenchID = OrgJobs.BenchID
GROUP BY SalaryData.BenchID, Benchmarks.CatID, SalaryData.OrgID
HAVING (((Benchmarks.CatID)=40) AND ((SalaryData.OrgID)=1083)) ]. AS
AllLatestRecords ON OrgJobs.BenchID = AllLatestRecords.BenchID) INNER JOIN
Benchmarks ON OrgJobs.BenchID = Benchmarks.BenchID
WHERE (((Benchmarks.CatID)=40));

I want to combine these into one single query. I have taken the entire
second query and used the following syntax...
[ text_of_Query1a_goes_here].AS Query1a

I then try to replace the Query1a in the Main query with the above syntax
....

So in the main Query, Instead of:
"...LEFT JOIN Query1a ON ...."
I Use
"...LEFT JOIN [ text_of_Query1a_goes_here].AS Query1a ON ...."

However, This gives me a syntax error (on the field name 'orgjobs')

You'll notice that the subquery 'Query1a' also references another sub query
called 'AllLatestRecords'. This one works fine when I embed it using the
same type of syntax.

I would greatly appreciate any help with this puzzle.

Best Regards,
Jack Coletti
St. Petersburg, FL
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't have square brackets in a derived table (a query w/ square
brackets around it). This means you can't have a derived table inside a
derived table. IOW, you can't have something like this:

SELECT *
FROM [SELECT ...
FROM [SELECT ...
]. As B
]. As A

And, you can't have this:

....
FROM [SELECT [column name] ... ]. As C
....

If you have Access 2002 and higher version, you can set the DB to use
ANSI SQL-92 option, then use parentheses instead of square brackets:

SELECT *
FROM (SELECT ...
FROM (SELECT ...
) As B
) As A

The following is OK
....
FROM (SELECT [column name] ... ) As C
....

WARNING: If you set the ANSI SQL-92 option on your current DB some of
your queries may stop working until you change them to SQL-92
compatibility.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlSQMIechKqOuFEgEQLD7wCg0euypQuC02rfqGJNEKQegdBIbE8AoIlE
vEzNrituzKqM84kW8aSqArGt
=L/6K
-----END PGP SIGNATURE-----

Hi Folks,

I have a complex query that references a sub-query. It works fine when I
call a sub-query from the main query (using design vew), but when I try to
embed the SQL text of the sub-query into the main query I get a syntax
error...

Here's the main Query. It works fine. It references another query from the
database called "Query1a"

SELECT Benchmarks.CatID, QueryA.*, Query1a.*, QueryA.Benchmarks.BenchID,
Query1a.Benchmarks.BenchID, Benchmarks.BenchID, Benchmarks.Benchmark
FROM (Benchmarks LEFT JOIN [SELECT Benchmarks.*, GroupJobs.*,
GroupJobs.GroupID, Benchmarks.CatID, Benchmarks.BenchID
FROM Benchmarks INNER JOIN GroupJobs ON Benchmarks.BenchID =
GroupJobs.BenchID
WHERE GroupJobs.GroupID=1AND Benchmarks.CatID=40]. AS QueryA ON
Benchmarks.BenchID = QueryA.Benchmarks.BenchID) LEFT JOIN Query1a ON
Benchmarks.BenchID = Query1a.Benchmarks.BenchID
WHERE (((Benchmarks.CatID)=40) AND ((QueryA.Benchmarks.BenchID) Is Not
Null)) OR (((Query1a.Benchmarks.BenchID) Is Not Null));

Here's Query1a...
SELECT AllLatestRecords.*, OrgJobs.*, Benchmarks.CatID, Benchmarks.BenchID
FROM (OrgJobs LEFT JOIN [SELECT Max(SalaryData.Dat) AS MaxOfDat,
SalaryData.BenchID, Benchmarks.CatID, SalaryData.OrgID
FROM Benchmarks INNER JOIN (OrgJobs INNER JOIN SalaryData ON OrgJobs.BenchID
= SalaryData.BenchID) ON Benchmarks.BenchID = OrgJobs.BenchID
GROUP BY SalaryData.BenchID, Benchmarks.CatID, SalaryData.OrgID
HAVING (((Benchmarks.CatID)=40) AND ((SalaryData.OrgID)=1083)) ]. AS
AllLatestRecords ON OrgJobs.BenchID = AllLatestRecords.BenchID) INNER JOIN
Benchmarks ON OrgJobs.BenchID = Benchmarks.BenchID
WHERE (((Benchmarks.CatID)=40));

I want to combine these into one single query. I have taken the entire
second query and used the following syntax...
[ text_of_Query1a_goes_here].AS Query1a

I then try to replace the Query1a in the Main query with the above syntax
...

So in the main Query, Instead of:
"...LEFT JOIN Query1a ON ...."
I Use
"...LEFT JOIN [ text_of_Query1a_goes_here].AS Query1a ON ...."

However, This gives me a syntax error (on the field name 'orgjobs')

You'll notice that the subquery 'Query1a' also references another sub query
called 'AllLatestRecords'. This one works fine when I embed it using the
same type of syntax.
Click to expand...
 
Hi MG,

This query is indended for use on an ASP page. Eventually, I intend to move
the whole thing to SQL Server database. Does this mean I can't use Access
2000 to develop the query? And if I get Access 2002, and use parentheses,
will the query work with SQL Server?

Thanks very much for your help.

Jack

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't have square brackets in a derived table (a query w/ square
brackets around it). This means you can't have a derived table inside a
derived table. IOW, you can't have something like this:

SELECT *
FROM [SELECT ...
FROM [SELECT ...
]. As B
]. As A

And, you can't have this:

...
FROM [SELECT [column name] ... ]. As C
...

If you have Access 2002 and higher version, you can set the DB to use
ANSI SQL-92 option, then use parentheses instead of square brackets:

SELECT *
FROM (SELECT ...
FROM (SELECT ...
) As B
) As A

The following is OK
...
FROM (SELECT [column name] ... ) As C
...

WARNING: If you set the ANSI SQL-92 option on your current DB some of
your queries may stop working until you change them to SQL-92
compatibility.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlSQMIechKqOuFEgEQLD7wCg0euypQuC02rfqGJNEKQegdBIbE8AoIlE
vEzNrituzKqM84kW8aSqArGt
=L/6K
-----END PGP SIGNATURE-----

Hi Folks,

I have a complex query that references a sub-query. It works fine when I
call a sub-query from the main query (using design vew), but when I try to
embed the SQL text of the sub-query into the main query I get a syntax
error...

Here's the main Query. It works fine. It references another query from the
database called "Query1a"

SELECT Benchmarks.CatID, QueryA.*, Query1a.*, QueryA.Benchmarks.BenchID,
Query1a.Benchmarks.BenchID, Benchmarks.BenchID, Benchmarks.Benchmark
FROM (Benchmarks LEFT JOIN [SELECT Benchmarks.*, GroupJobs.*,
GroupJobs.GroupID, Benchmarks.CatID, Benchmarks.BenchID
FROM Benchmarks INNER JOIN GroupJobs ON Benchmarks.BenchID =
GroupJobs.BenchID
WHERE GroupJobs.GroupID=1AND Benchmarks.CatID=40]. AS QueryA ON
Benchmarks.BenchID = QueryA.Benchmarks.BenchID) LEFT JOIN Query1a ON
Benchmarks.BenchID = Query1a.Benchmarks.BenchID
WHERE (((Benchmarks.CatID)=40) AND ((QueryA.Benchmarks.BenchID) Is Not
Null)) OR (((Query1a.Benchmarks.BenchID) Is Not Null));

Here's Query1a...
SELECT AllLatestRecords.*, OrgJobs.*, Benchmarks.CatID, Benchmarks.BenchID
FROM (OrgJobs LEFT JOIN [SELECT Max(SalaryData.Dat) AS MaxOfDat,
SalaryData.BenchID, Benchmarks.CatID, SalaryData.OrgID
FROM Benchmarks INNER JOIN (OrgJobs INNER JOIN SalaryData ON OrgJobs.BenchID
= SalaryData.BenchID) ON Benchmarks.BenchID = OrgJobs.BenchID
GROUP BY SalaryData.BenchID, Benchmarks.CatID, SalaryData.OrgID
HAVING (((Benchmarks.CatID)=40) AND ((SalaryData.OrgID)=1083)) ]. AS
AllLatestRecords ON OrgJobs.BenchID = AllLatestRecords.BenchID) INNER JOIN
Benchmarks ON OrgJobs.BenchID = Benchmarks.BenchID
WHERE (((Benchmarks.CatID)=40));

I want to combine these into one single query. I have taken the entire
second query and used the following syntax...
[ text_of_Query1a_goes_here].AS Query1a

I then try to replace the Query1a in the Main query with the above syntax
...

So in the main Query, Instead of:
"...LEFT JOIN Query1a ON ...."
I Use
"...LEFT JOIN [ text_of_Query1a_goes_here].AS Query1a ON ...."

However, This gives me a syntax error (on the field name 'orgjobs')

You'll notice that the subquery 'Query1a' also references another sub query
called 'AllLatestRecords'. This one works fine when I embed it using the
same type of syntax.
Click to expand...
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SQL Server 2000 uses the ANSI SQL-92 standard. If you use the square
bracket derived tables you'll have to change them to the SQL-92 standard
before SQL'r will run them. Also, any JET/VBA functions will have to be
converted to SQL'r equivalent functions.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlWEw4echKqOuFEgEQJuNwCg70EufuCQ+pnR3n2Rk9yasVyTDhgAoKUF
qhOJKTZwXSSyidviNSK+faQZ
=Q0Jb
-----END PGP SIGNATURE-----
Hi MG,

This query is indended for use on an ASP page. Eventually, I intend to move
the whole thing to SQL Server database. Does this mean I can't use Access
2000 to develop the query? And if I get Access 2002, and use parentheses,
will the query work with SQL Server?

Thanks very much for your help.

Jack

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't have square brackets in a derived table (a query w/ square
brackets around it). This means you can't have a derived table inside a
derived table. IOW, you can't have something like this:

SELECT *
FROM [SELECT ...
FROM [SELECT ...
]. As B
]. As A

And, you can't have this:

...
FROM [SELECT [column name] ... ]. As C
...

If you have Access 2002 and higher version, you can set the DB to use
ANSI SQL-92 option, then use parentheses instead of square brackets:

SELECT *
FROM (SELECT ...
FROM (SELECT ...
) As B
) As A

The following is OK
...
FROM (SELECT [column name] ... ) As C
...

WARNING: If you set the ANSI SQL-92 option on your current DB some of
your queries may stop working until you change them to SQL-92
compatibility.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlSQMIechKqOuFEgEQLD7wCg0euypQuC02rfqGJNEKQegdBIbE8AoIlE
vEzNrituzKqM84kW8aSqArGt
=L/6K
-----END PGP SIGNATURE-----

Hi Folks,

I have a complex query that references a sub-query. It works fine when
Click to expand...
I
call a sub-query from the main query (using design vew), but when I try
Click to expand...
to
embed the SQL text of the sub-query into the main query I get a syntax
error...

Here's the main Query. It works fine. It references another query from
Click to expand...
the
database called "Query1a"

SELECT Benchmarks.CatID, QueryA.*, Query1a.*, QueryA.Benchmarks.BenchID,
Query1a.Benchmarks.BenchID, Benchmarks.BenchID, Benchmarks.Benchmark
FROM (Benchmarks LEFT JOIN [SELECT Benchmarks.*, GroupJobs.*,
GroupJobs.GroupID, Benchmarks.CatID, Benchmarks.BenchID
FROM Benchmarks INNER JOIN GroupJobs ON Benchmarks.BenchID =
GroupJobs.BenchID
WHERE GroupJobs.GroupID=1AND Benchmarks.CatID=40]. AS QueryA ON
Benchmarks.BenchID = QueryA.Benchmarks.BenchID) LEFT JOIN Query1a ON
Benchmarks.BenchID = Query1a.Benchmarks.BenchID
WHERE (((Benchmarks.CatID)=40) AND ((QueryA.Benchmarks.BenchID) Is Not
Null)) OR (((Query1a.Benchmarks.BenchID) Is Not Null));

Here's Query1a...
SELECT AllLatestRecords.*, OrgJobs.*, Benchmarks.CatID,
Click to expand...
Benchmarks.BenchID
FROM (OrgJobs LEFT JOIN [SELECT Max(SalaryData.Dat) AS MaxOfDat,
SalaryData.BenchID, Benchmarks.CatID, SalaryData.OrgID
FROM Benchmarks INNER JOIN (OrgJobs INNER JOIN SalaryData ON
Click to expand...
OrgJobs.BenchID
= SalaryData.BenchID) ON Benchmarks.BenchID = OrgJobs.BenchID
GROUP BY SalaryData.BenchID, Benchmarks.CatID, SalaryData.OrgID
HAVING (((Benchmarks.CatID)=40) AND ((SalaryData.OrgID)=1083)) ]. AS
AllLatestRecords ON OrgJobs.BenchID = AllLatestRecords.BenchID) INNER
Click to expand...
JOIN
Benchmarks ON OrgJobs.BenchID = Benchmarks.BenchID
WHERE (((Benchmarks.CatID)=40));

I want to combine these into one single query. I have taken the entire
second query and used the following syntax...
[ text_of_Query1a_goes_here].AS Query1a

I then try to replace the Query1a in the Main query with the above
Click to expand...
syntax
...

So in the main Query, Instead of:
"...LEFT JOIN Query1a ON ...."
I Use
"...LEFT JOIN [ text_of_Query1a_goes_here].AS Query1a ON ...."

However, This gives me a syntax error (on the field name 'orgjobs')

You'll notice that the subquery 'Query1a' also references another sub
Click to expand...
query
called 'AllLatestRecords'. This one works fine when I embed it using
Click to expand...
the
same type of syntax.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top