Question about Queries with Subqueries

D

DawnTreader

Hello All

i have a query i am working on that draws data from 3 different data
"paths". i am using a query with a union subquery to pull the information i
need. it looks like this:

SELECT
SQ.ProdID,
SQ.Category,
SQ.IMWPNID,
SQ.PartDesc,
SQ.Ref,
SQ.PartBlockQTY,
SQ.ListPrice,
SQ.PLID,
nz([SQ].[PartBlockQTY],0)*nz([SQ].[ListPrice],0) AS SkidValue,
tblMasterPartList.[1000Maint] AS Maint1000Hr,
tblMasterPartList.[5000Rebuild] AS Rebuild5000Hr,
tblMasterPartList.[10000Rebuild] AS Rebuild10000Hr,
tblMasterPartList.[15000Rebuild] AS Rebuild15000Hr,
tblMasterPartList.[20000Rebuild] AS Rebuild20000Hr,
tblMasterPartList.[25000Rebuild] AS Rebuild25000Hr
FROM
[SELECT
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"" AS Ref,
tblProductPartList.QTY AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
tblProductPartList.PartListID AS PLID
FROM
subtblSectionName INNER JOIN (tblProductPartList LEFT JOIN dbo_PART
ON tblProductPartList.IMWPartNumberID = dbo_PART.ID)
ON subtblSectionName.SectionNameID = tblProductPartList.SectionNameID
WHERE
(((tblProductPartList.IMWPartNumberID) Is Not Null))

UNION SELECT
tblProductPartList.ProductID AS ProdID,
tblProductPartList.RequirementCategory AS Category,
dbo_REQUIREMENT.PART_ID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
dbo_REQUIREMENT.REFERENCE AS Ref,
dbo_REQUIREMENT.QTY_PER AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
tblProductPartList.PartListID AS PLID
FROM
(tblProductPartList LEFT JOIN dbo_REQUIREMENT
ON tblProductPartList.RequirementID = dbo_REQUIREMENT.ROWID)
LEFT JOIN dbo_PART ON dbo_REQUIREMENT.PART_ID = dbo_PART.ID
WHERE
(((tblProductPartList.RequirementID) Is Not Null))

UNION SELECT
tblProductList.ProductID AS ProdID,
"CBA" AS Category,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"CBA" AS Ref,
tblCBAPartList.QTY AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
CBAPartListID AS PLID
FROM
((tblCBBANumbers LEFT JOIN tblCBAPartList
ON tblCBBANumbers.CBBAID = tblCBAPartList.CBAID)
RIGHT JOIN (tblProductList LEFT JOIN subdatatblCompressor
ON (tblProductList.ProductTypeID = subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID))
ON tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN dbo_PART ON tblCBAPartList.IMWPartNumberID = dbo_PART.ID
WHERE
(((tblProductPartList.IMWPartNumberID) Is Null)) AND
(((tblProductPartList.RequirementID) Is Null))]. AS SQ LEFT JOIN
tblMasterPartList ON SQ.IMWPNID = tblMasterPartList.ID;

i have a question about queries like this. first a little background

i have built this multiple times trying to find the most efficient way to
get the information in the format that i need. originally i had 7 levels of
queries to get the result i am looking for. the problem is the 7th level gets
so complex that it chokes on the "system resources exceeded" error and if i
add another table it stops on "cannot open anymore databases". so i decided
to try "flattening" my queries by using subqueries.

although the SQL above works there are 2 things i need to know before going
further. the first is, how many subqueries can i do in SQL? the second
question, will i hit the same problem "Cannot open anymore databases" with
one query that is built with a few subqueries and subsubqueries?

unrelated, how do i get the third part of the union query to only show those
parts not generated by the other 2 parts of the union query?
 
S

Sylvain Lafontaine

From http://office.microsoft.com/en-us/access/HA100307391033.aspx , the
indicated maximum number of levels for nested queries is 50 but in practice,
probably that you will be hit by resources limits or that Access will crash
on you well before you'ill reach this limit.

Also, I'm not sure but in your case, maybe it will be advantageous for
performance reasons to use UNION ALL instead of UNION.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


DawnTreader said:
Hello All

i have a query i am working on that draws data from 3 different data
"paths". i am using a query with a union subquery to pull the information
i
need. it looks like this:

SELECT
SQ.ProdID,
SQ.Category,
SQ.IMWPNID,
SQ.PartDesc,
SQ.Ref,
SQ.PartBlockQTY,
SQ.ListPrice,
SQ.PLID,
nz([SQ].[PartBlockQTY],0)*nz([SQ].[ListPrice],0) AS SkidValue,
tblMasterPartList.[1000Maint] AS Maint1000Hr,
tblMasterPartList.[5000Rebuild] AS Rebuild5000Hr,
tblMasterPartList.[10000Rebuild] AS Rebuild10000Hr,
tblMasterPartList.[15000Rebuild] AS Rebuild15000Hr,
tblMasterPartList.[20000Rebuild] AS Rebuild20000Hr,
tblMasterPartList.[25000Rebuild] AS Rebuild25000Hr
FROM
[SELECT
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"" AS Ref,
tblProductPartList.QTY AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
tblProductPartList.PartListID AS PLID
FROM
subtblSectionName INNER JOIN (tblProductPartList LEFT JOIN dbo_PART
ON tblProductPartList.IMWPartNumberID = dbo_PART.ID)
ON subtblSectionName.SectionNameID = tblProductPartList.SectionNameID
WHERE
(((tblProductPartList.IMWPartNumberID) Is Not Null))

UNION SELECT
tblProductPartList.ProductID AS ProdID,
tblProductPartList.RequirementCategory AS Category,
dbo_REQUIREMENT.PART_ID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
dbo_REQUIREMENT.REFERENCE AS Ref,
dbo_REQUIREMENT.QTY_PER AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
tblProductPartList.PartListID AS PLID
FROM
(tblProductPartList LEFT JOIN dbo_REQUIREMENT
ON tblProductPartList.RequirementID = dbo_REQUIREMENT.ROWID)
LEFT JOIN dbo_PART ON dbo_REQUIREMENT.PART_ID = dbo_PART.ID
WHERE
(((tblProductPartList.RequirementID) Is Not Null))

UNION SELECT
tblProductList.ProductID AS ProdID,
"CBA" AS Category,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"CBA" AS Ref,
tblCBAPartList.QTY AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
CBAPartListID AS PLID
FROM
((tblCBBANumbers LEFT JOIN tblCBAPartList
ON tblCBBANumbers.CBBAID = tblCBAPartList.CBAID)
RIGHT JOIN (tblProductList LEFT JOIN subdatatblCompressor
ON (tblProductList.ProductTypeID = subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID))
ON tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN dbo_PART ON tblCBAPartList.IMWPartNumberID = dbo_PART.ID
WHERE
(((tblProductPartList.IMWPartNumberID) Is Null)) AND
(((tblProductPartList.RequirementID) Is Null))]. AS SQ LEFT JOIN
tblMasterPartList ON SQ.IMWPNID = tblMasterPartList.ID;

i have a question about queries like this. first a little background

i have built this multiple times trying to find the most efficient way to
get the information in the format that i need. originally i had 7 levels
of
queries to get the result i am looking for. the problem is the 7th level
gets
so complex that it chokes on the "system resources exceeded" error and if
i
add another table it stops on "cannot open anymore databases". so i
decided
to try "flattening" my queries by using subqueries.

although the SQL above works there are 2 things i need to know before
going
further. the first is, how many subqueries can i do in SQL? the second
question, will i hit the same problem "Cannot open anymore databases" with
one query that is built with a few subqueries and subsubqueries?

unrelated, how do i get the third part of the union query to only show
those
parts not generated by the other 2 parts of the union query?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to use WHERE NOT EXISTS 2
is this query with a subquery, a union query, normal? 1
Read Only Query, need a little help 1
Heavy data munching - is there a more efficient way of doing this? 8
Concatenating the same field from multiple records 16
How to fix the one exception to the rule. 0
help with figuring out how to make subqueries 0

Top