Home Forums SQL Server 2005 T-SQL (SS2K5) SET value WHERE Product has > 2 Suppliers RE: SET value WHERE Product has > 2 Suppliers

  • February 6, 2009 at 8:43 am

    #939592

    Try:

    SELECT dbo.forum.Product

    ,dbo.forum.Supplier

    ,dbo.forum.SupplierCrossReference

    ,dbo.forum.DateLastQuoted

    FROM dbo.forum

    WHERE SupplierSequence = '500'

    AND Product IN (SELECT Product

    FROM dbo.Forum

    WHERE SupplierSequence = '500'

    GROUP BY Product

    HAVING COUNT(1) > 1)

    or

    SELECT dbo.forum.Product

    ,dbo.forum.Supplier

    ,dbo.forum.SupplierCrossReference

    ,dbo.forum.DateLastQuoted

    FROM dbo.forum

    INNER JOIN (SELECT Product

    ,MAX(DateLastQuoted) DateLastQuoted

    FROM dbo.Forum

    WHERE SupplierSequence = '500'

    GROUP BY Product

    HAVING COUNT(1) > 1) p

    ON p.Product = dbo.forum.Product

    AND p.DateLastQuoted = dbo.forum.DateLastQuoted

    WHERE dbo.forum.SupplierSequence = '500'