Post reply

Remove records where not have same value in common.

  • May 12, 2009 at 8:41 am

    #134222

    I have 3 fields (with sample data)

    RegimenID, DrugID, Country

    =======================

    1, 22, US

    1, 22, France

    1, 22, Germany

    1, 34, US

    1, 34, France

    2, 22, US

    2, 22 Germany

    2, 44 US

    What I need is a delete query to delete drug records for a given regimen where the countries are not shared across all drugs for that regimenid.

    Thus, 1, 22, Germany would be deleted from regimenid = 1, and 2, 22 Germany would be deleted from regimenid = 2.

  • May 12, 2009 at 2:43 pm

    #993284

    This method is just my first thought... and I expect there is a more elegant and efficient solution. This method will also assume that the table has unique rows by Regimen, Drug, Country. If you uncomment the additional Germany record you will see the impact of this assumption. This is not a delete statement, but gets you the result and you can put it into a delete query.

    DECLARE @tbl AS TABLE (RegimenId INT, DrugId INT, Country VARCHAR(20))

    INSERT INTO @tbl

    SELECT 1, 22, 'US'

    UNION ALL

    SELECT 1, 22, 'France'

    UNION ALL

    SELECT 1, 22, 'Germany'

    UNION ALL

    SELECT 1, 34, 'US'

    UNION ALL

    SELECT 1, 34, 'France'

    UNION ALL

    SELECT 2, 22, 'US'

    UNION ALL

    SELECT 2, 22, 'Germany'

    --UNION ALL

    --SELECT 2, 22, 'Germany'

    UNION ALL

    SELECT 2, 44, 'US'

    SELECT *

    FROM @Tbl

    SELECT t1.RegimenId, t1.Country

    FROM @Tbl t1

    GROUP BY t1.RegimenId, t1.COUNTRY

    HAVING COUNT(*) < (SELECT COUNT(DISTINCT DrugId)

    FROM @Tbl t2

    WHERE t2.RegimenId = t1.RegimenId)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply