Heavy Duty Union Subqueries

D

DawnTreader

Hello All

i have need to make this work:

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,
tblCBAPartList.CBAPartListID AS PLID

FROM
(tblCBBANumbers RIGHT JOIN ((tblProductList LEFT JOIN

[SELECT ALL
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) AND
tblProductPartList.ProductID=669)
UNION SELECT ALL
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"Manual" 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) AND
tblProductPartList.ProductID=669)]. AS SQ1

ON tblProductList.ProductID = SQ1.ProdID) LEFT JOIN subdatatblCompressor
ON (tblProductList.ProductTypeID = subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID)) ON
tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN (tblCBAPartList LEFT JOIN dbo_PART ON
tblCBAPartList.IMWPartNumberID = dbo_PART.ID) ON tblCBBANumbers.CBBAID =
tblCBAPartList.CBAID
WHERE
(((tblProductList.ProductID)=669) AND ((SQ1.IMWPNID) Is Null));

the subquery should show everything, and the main query should only show
what is not already in the subquery. i used another union query i made as the
basis for this, but this one isnt working. any suggestions?

i know it has to do with the SQ1.IMWPNID is null line, but i am unsure of
what to change it to, or if the query needs to be restructured, ie i have the
wrong dataset being the subquery.

any and all help appreciated.
 
S

Sylvain Lafontaine

It's "UNION ALL Select ...", not "UNION SELECT ALL ...". You also have to
correct the subquery "[Select All ...".
 
S

Sylvain Lafontaine

Also, you're still using a combination of Right Join with Left Join.
Replace the Right Join with a Left Join and probably that your query will be
ok after that.

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


DawnTreader said:
Hello All

i have need to make this work:

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,
tblCBAPartList.CBAPartListID AS PLID

FROM
(tblCBBANumbers RIGHT JOIN ((tblProductList LEFT JOIN

[SELECT ALL
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) AND
tblProductPartList.ProductID=669)
UNION SELECT ALL
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"Manual" 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) AND
tblProductPartList.ProductID=669)]. AS SQ1

ON tblProductList.ProductID = SQ1.ProdID) LEFT JOIN subdatatblCompressor
ON (tblProductList.ProductTypeID = subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID)) ON
tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN (tblCBAPartList LEFT JOIN dbo_PART ON
tblCBAPartList.IMWPartNumberID = dbo_PART.ID) ON tblCBBANumbers.CBBAID =
tblCBAPartList.CBAID
WHERE
(((tblProductList.ProductID)=669) AND ((SQ1.IMWPNID) Is Null));

the subquery should show everything, and the main query should only show
what is not already in the subquery. i used another union query i made as
the
basis for this, but this one isnt working. any suggestions?

i know it has to do with the SQ1.IMWPNID is null line, but i am unsure of
what to change it to, or if the query needs to be restructured, ie i have
the
wrong dataset being the subquery.

any and all help appreciated.
Click to expand...
 
D

DawnTreader

Hello Again

it will not allow me to use a left join.

i assume you mean this one:

(tblCBBANumbers RIGHT JOIN ((tblProductList LEFT JOIN

and if you do it will not allow me to use a left in place of the right. now
that may be due to the SQL syntax and the fact that i need to move code
around, but i dont know where to move things around and the syntax that i
would need.

the other question i have is how do i turn this into a make table query?

Sylvain Lafontaine said:
Also, you're still using a combination of Right Join with Left Join.
Replace the Right Join with a Left Join and probably that your query will be
ok after that.

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


DawnTreader said:
Hello All

i have need to make this work:

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,
tblCBAPartList.CBAPartListID AS PLID

FROM
(tblCBBANumbers RIGHT JOIN ((tblProductList LEFT JOIN

[SELECT ALL
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) AND
tblProductPartList.ProductID=669)
UNION SELECT ALL
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"Manual" 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) AND
tblProductPartList.ProductID=669)]. AS SQ1

ON tblProductList.ProductID = SQ1.ProdID) LEFT JOIN subdatatblCompressor
ON (tblProductList.ProductTypeID = subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID)) ON
tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN (tblCBAPartList LEFT JOIN dbo_PART ON
tblCBAPartList.IMWPartNumberID = dbo_PART.ID) ON tblCBBANumbers.CBBAID =
tblCBAPartList.CBAID
WHERE
(((tblProductList.ProductID)=669) AND ((SQ1.IMWPNID) Is Null));

the subquery should show everything, and the main query should only show
what is not already in the subquery. i used another union query i made as
the
basis for this, but this one isnt working. any suggestions?

i know it has to do with the SQ1.IMWPNID is null line, but i am unsure of
what to change it to, or if the query needs to be restructured, ie i have
the
wrong dataset being the subquery.

any and all help appreciated.
Click to expand...
Click to expand...
 
D

DawnTreader

dang, actually this may not be doing what i need at all.

the part:

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, tblCBAPartList.CBAPartListID AS PLID

needs to only show parts that are not in the other list created by:

[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) AND
tblProductPartList.ProductID=669)
UNION ALL SELECT
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"Manual" 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) AND
tblProductPartList.ProductID=669)]. AS SQ1

do i have the right thing as the subquery? should this be flipped around?
here is the SQL as it stands now:

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,
tblCBAPartList.CBAPartListID AS PLID
FROM (tblCBBANumbers RIGHT JOIN ((tblProductList RIGHT JOIN [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) AND
tblProductPartList.ProductID=669)
UNION ALL SELECT
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"Manual" 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) AND
tblProductPartList.ProductID=669)]. AS SQ1 ON tblProductList.ProductID =
SQ1.ProdID)
LEFT JOIN subdatatblCompressor ON (tblProductList.ProductTypeID =
subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID))
ON tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN (tblCBAPartList LEFT JOIN dbo_PART ON
tblCBAPartList.IMWPartNumberID = dbo_PART.ID) ON tblCBBANumbers.CBBAID =
tblCBAPartList.CBAID
WHERE (((tblProductList.ProductID)=669) AND ((SQ1.IMWPNID) Is Null));

and notice i did get the right join in after all. i went to the QBE grid and
flipped it there. however i am not getting the results i need.

Sylvain Lafontaine said:
Also, you're still using a combination of Right Join with Left Join.
Replace the Right Join with a Left Join and probably that your query will be
ok after that.

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


DawnTreader said:
Hello All

i have need to make this work:

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,
tblCBAPartList.CBAPartListID AS PLID

FROM
(tblCBBANumbers RIGHT JOIN ((tblProductList LEFT JOIN

[SELECT ALL
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) AND
tblProductPartList.ProductID=669)
UNION SELECT ALL
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"Manual" 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) AND
tblProductPartList.ProductID=669)]. AS SQ1

ON tblProductList.ProductID = SQ1.ProdID) LEFT JOIN subdatatblCompressor
ON (tblProductList.ProductTypeID = subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID)) ON
tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN (tblCBAPartList LEFT JOIN dbo_PART ON
tblCBAPartList.IMWPartNumberID = dbo_PART.ID) ON tblCBBANumbers.CBBAID =
tblCBAPartList.CBAID
WHERE
(((tblProductList.ProductID)=669) AND ((SQ1.IMWPNID) Is Null));

the subquery should show everything, and the main query should only show
what is not already in the subquery. i used another union query i made as
the
basis for this, but this one isnt working. any suggestions?

i know it has to do with the SQ1.IMWPNID is null line, but i am unsure of
what to change it to, or if the query needs to be restructured, ie i have
the
wrong dataset being the subquery.

any and all help appreciated.
Click to expand...
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
Question about Queries with Subqueries 7
is this query with a subquery, a union query, normal? 1
Heavy data munching - is there a more efficient way of doing this? 8
Concatenating the same field from multiple records 16
Read Only Query, need a little help 1

Top