|
|
Posted by Erland Sommarskog on 06/08/05 00:36
serge (sergea@nospam.ehmail.com) writes:
> with this new one:
>
> -- Insert all details where there detail id is bigger than the min
> -- detail-id for for a product.
> INSERT #newprod (oldprodid, detailid, name)
> SELECT a.ProductID, a.UniqueID, a.Name
> FROM
>
> (SELECT tblProductDetails.ProductId, tblProductDetails.UniqueId,
> tblProductDetails.Name
> FROM tblProductDetails INNER JOIN tblProducts
> ON tblProductDetails.ProductId = tblProducts.UniqueId
> WHERE tblProductDetails.Name <> tblProducts.Name) AS a
>
> INNER JOIN
>
> (SELECT ProductID
> FROM tblProductDetails
> GROUP BY ProductID
> HAVING COUNT(*) > 1) AS b
> ON a.ProductId = b.ProductId
>
> ORDER BY a.UniqueID
>
>
> Basically my first derived table "a" returns me all tblProductDetails
> where the Name is different than the Name in tblProducts.
>
> Then I need to reduce the list of the derived table "a" to those records
> that exist more than 1 record (JOIN to derived table "b").
Excellent! Don't know if I would have been able to come with that
one by myself. (Well, maybe I would, but I was not thinking those
directions.)
Here is a rewrite of the query, more for clarity to tell exactly
what is going on: Save all product details where the name differs
from the product, for which there is more than one row for the
product:
INSERT #newprod (oldprodid, detailid, name)
SELECT a.ProductID, a.UniqueID, a.Name
FROM (SELECT d.ProductID, d.UniqueID, d.Name
FROM tblProductDetails d
JOIN tblProducts p ON d.ProductID = p.UniqueID
WHERE d.Name <> p.Name) AS a
WHERE EXISTS (SELECT *
FROM (SELECT ProductID
FROM tblProductDetails
GROUP BY ProductID
HAVING COUNT(*) > 1) AS b
WHERE a.ProductID = b.ProductID)
ORDER BY a.UniqueID
Whether one likes alias or is partly a matter of taste, but I think
the code is more consice and easier to read this way.
Thanks for posting back the final result!
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[Back to original message]
|