How to use WHERE NOT EXISTS

D

DawnTreader

Hello All

i have need to make this SQL work. i have 3 sets of data, 2 sets will never
have the same data, the 3rd can duplicate things that are in the other 2
sets. i need to combine all 3 sets into one set that has no duplicates from
the 3rd set.

additionally after combining the 3 sets i need to add some additional
related fields based on the part numbers in the resulting set of data from
the 3 non dupe sets.

here is my SQL as it currently stands:

SELECT
SQ1.ProdID,
SQ1.Category,
SQ1.IMWPNID,
SQ1.PartDesc,
SQ1.Ref,
SQ1.PartBlockQTY,
SQ1.ListPrice,
SQ1.PLID,
nz([SQ1].[PartBlockQTY],0)*nz([SQ1].[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
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 AND
(UNION ALL 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) AND
tblProductPartList.ProductID=669)

UNION ALL 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)) AS SQ
WHERE NOT EXISTS tblCBAPartLIst.IMWPartNumberID) AS SQ1 LEFT JOIN
tblMasterPartList ON SQ1.IMWPNID = tblMasterPartList.ID
ORDER BY SQ1.ProdID;

you will notice that there are 2 subqueries. i am trying to figure out how
to get the second subquery into a place to enable it to retrive the records
in the 2 data sets that have no dupes, and then find all the non dupes in the
3rd data set and then bring it all together with the additional fields. the
problem is i dont know where to put the SQ sub-subquery inside of the SQ1
subquery and how to filter it based on the results of the SQ sub-subquery.

any and all help appreciated.
 
D

DawnTreader

Hello Again

i have tried this:

SELECT SQ1.ProdID, SQ1.Category, SQ1.IMWPNID, SQ1.PartDesc, SQ1.Ref,
SQ1.PartBlockQTY, SQ1.ListPrice, SQ1.PLID,
nz([SQ1].[PartBlockQTY],0)*nz([SQ1].[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
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
(tblProductList.ProductID=669)
AND tblCBAPartList.IMWPartNumberID NOT IN (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) AND
tblProductPartList.ProductID=669)

UNION ALL 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)) AS SQ
WHERE NOT EXISTS tblCBAPartLIst.IMWPartNumberID) AS SQ1 LEFT JOIN
tblMasterPartList ON SQ1.IMWPNID = tblMasterPartList.ID
ORDER BY SQ1.ProdID;


and gotten an error saying "this action is not allowed in subqueries".

any ideas?

DawnTreader said:
Hello All

i have need to make this SQL work. i have 3 sets of data, 2 sets will never
have the same data, the 3rd can duplicate things that are in the other 2
sets. i need to combine all 3 sets into one set that has no duplicates from
the 3rd set.

additionally after combining the 3 sets i need to add some additional
related fields based on the part numbers in the resulting set of data from
the 3 non dupe sets.

here is my SQL as it currently stands:

SELECT
SQ1.ProdID,
SQ1.Category,
SQ1.IMWPNID,
SQ1.PartDesc,
SQ1.Ref,
SQ1.PartBlockQTY,
SQ1.ListPrice,
SQ1.PLID,
nz([SQ1].[PartBlockQTY],0)*nz([SQ1].[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
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 AND
(UNION ALL 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) AND
tblProductPartList.ProductID=669)

UNION ALL 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)) AS SQ
WHERE NOT EXISTS tblCBAPartLIst.IMWPartNumberID) AS SQ1 LEFT JOIN
tblMasterPartList ON SQ1.IMWPNID = tblMasterPartList.ID
ORDER BY SQ1.ProdID;

you will notice that there are 2 subqueries. i am trying to figure out how
to get the second subquery into a place to enable it to retrive the records
in the 2 data sets that have no dupes, and then find all the non dupes in the
3rd data set and then bring it all together with the additional fields. the
problem is i dont know where to put the SQ sub-subquery inside of the SQ1
subquery and how to filter it based on the results of the SQ sub-subquery.

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

DawnTreader

Whoops...

didnt format that last well. sorry...

i noticed a problem with my where near the end of the sql... here is a fix.

SELECT
SQ1.ProdID,
SQ1.Category,
SQ1.IMWPNID,
SQ1.PartDesc,
SQ1.Ref,
SQ1.PartBlockQTY,
SQ1.ListPrice,
SQ1.PLID,
nz([SQ1].[PartBlockQTY],0)*nz([SQ1].[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
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
(tblProductList.ProductID=669)
AND tblCBAPartList.IMWPartNumberID NOT IN (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) AND
tblProductPartList.ProductID=669)

UNION ALL 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)) AS SQ) AS SQ1 LEFT JOIN tblMasterPartList
ON SQ1.IMWPNID = tblMasterPartList.ID
ORDER BY SQ1.ProdID;

still gets the same error though. :(

DawnTreader said:
Hello All

i have need to make this SQL work. i have 3 sets of data, 2 sets will never
have the same data, the 3rd can duplicate things that are in the other 2
sets. i need to combine all 3 sets into one set that has no duplicates from
the 3rd set.

additionally after combining the 3 sets i need to add some additional
related fields based on the part numbers in the resulting set of data from
the 3 non dupe sets.

here is my SQL as it currently stands:

SELECT
SQ1.ProdID,
SQ1.Category,
SQ1.IMWPNID,
SQ1.PartDesc,
SQ1.Ref,
SQ1.PartBlockQTY,
SQ1.ListPrice,
SQ1.PLID,
nz([SQ1].[PartBlockQTY],0)*nz([SQ1].[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
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 AND
(UNION ALL 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) AND
tblProductPartList.ProductID=669)

UNION ALL 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)) AS SQ
WHERE NOT EXISTS tblCBAPartLIst.IMWPartNumberID) AS SQ1 LEFT JOIN
tblMasterPartList ON SQ1.IMWPNID = tblMasterPartList.ID
ORDER BY SQ1.ProdID;

you will notice that there are 2 subqueries. i am trying to figure out how
to get the second subquery into a place to enable it to retrive the records
in the 2 data sets that have no dupes, and then find all the non dupes in the
3rd data set and then bring it all together with the additional fields. the
problem is i dont know where to put the SQ sub-subquery inside of the SQ1
subquery and how to filter it based on the results of the SQ sub-subquery.

any and all help appreciated.
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

Heavy Duty Union Subqueries 4
Question about Queries with Subqueries 7
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

Top