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.
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.