Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    3

    Unhappy Unanswered: Matching Columns

    All

    I have a table which contains 4 columns each of which are NULL or contain a 6 digit code.
    Here is a sample of the table content:

    COL1 COL2 COL3 COL4
    ----- ----- ----- -----
    452359 NULL NULL 347406
    NULL NULL 347406 347406
    592319 NULL 347406 347406
    592319 150009 347406 347406
    592319 150010 347406 347406

    Through out the table any number of the columns can be null.

    I wish to remove rows from this table where the columns values are contained in another row i.e Row 2 above is contained within row 3. Similarly, row 3 is contained within row 4.
    So, the only rows I want from the sample data above are rows 1, 4 and 5.

    I hope I have explained my query adequately, and any help would be great appreciated. (Before I go mad...!)

    Regards,
    Katherine

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You'll have to test this carefully, but I'd use something like:
    Code:
    DELETE FROM foo
       WHERE (Col1 IS NULL OR Col2 IS NULL OR Col3 IS NULL OR Col4 IS NULL)
          AND EXISTS (SELECT *
             FROM foo AS b
             WHERE  b.Col1 = Coalesce(foo.Col1, b.Col1)
                AND b.Col2 = Coalesce(foo.Col2, b.Col2)
                AND b.Col3 = Coalesce(foo.Col3, b.Col3)
                AND b.Col4 = Coalesce(foo.Col4, b.Col4))
    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    pat: sorry:
    insert...
    select 123, null, null, null
    union
    select null, 123, null, null
    union
    select 123, 123, null, null

    gives:
    col1 col2 col3 col4
    ----------- ----------- ----------- -----------
    NULL 123 NULL NULL
    123 NULL NULL NULL
    123 123 NULL NULL


    Turns out it's a nice challenge, at least for me...

  4. #4
    Join Date
    Aug 2004
    Posts
    8
    I know there *has* to be a better way to do this... but here goes.

    create table test (a int null, b int null, c int null, d int null)

    insert into test(a,b,c,d)
    select 123, null, null, null
    union
    select null, 123, null, null
    union
    select 123, 123, null, null

    delete from t1
    from test t1
    inner join test t2
    on (t1.a is null or t1.a=t2.a)
    and (t1.b is null or t1.b=t2.b)
    and (t1.c is null or t1.c=t2.c)
    and (t1.d is null or t1.d=t2.d)
    where
    (
    ((t1.a!=t2.a and (t1.a is not null and t2.a is not null)) or (t1.a is null and t2.a is not null) or (t1.a is not null and t2.a is null))
    or ((t1.b!=t2.b and (t1.b is not null and t2.b is not null)) or (t1.b is null and t2.b is not null) or (t1.b is not null and t2.b is null))
    or ((t1.c!=t2.c and (t1.c is not null and t2.c is not null)) or (t1.c is null and t2.c is not null) or (t1.c is not null and t2.c is null))
    or ((t1.d!=t2.d and (t1.d is not null and t2.d is not null)) or (t1.d is null and t2.d is not null) or (t1.d is not null and t2.d is null))
    )

    You'd have to run a dedup after this if you've got duplicate rows.
    That ugly WHERE is just killin' me, but dealing with the NULLs is a real PITA.

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    I tried it by creating a new table, inserting those with the least nulls then winding up to the most nulls skipping those with matching values. The way I have it now is a real pain and doesn't work properly.. so...

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Like a lot of tricky SQL problems, the issue here is not the question you are asking, but the fact that your schema is not normalized. Fix it, if at all possible, or you are sure to run into more problems in the future.

    This method solves your problem but normalizing your data into a temporary table, and then searching the temporary table for record groups that are a subset of other record groups. It presupposes that you have a unique primary key in your original table.

    DECLARE @TEMPNORMAL TABLE
    (PKEY INT,
    COLVALUE VARCHAR(6))

    DECLARE @PKEYITEMCOUNT TABLE
    (PKEY INT,
    ITEMCOUNT INT)

    INSERT INTO @TEMPNORMAL
    (PKEY,
    COLVALUE)
    SELECT DISTINCT
    PKEY,
    COLVALUE
    FROM
    (Select PKEY, COL1 as COLVALUE from YOURTABLE
    UNION
    Select PKEY, COL2 as COLVALUE from YOURTABLE
    UNION
    Select PKEY, COL3 as COLVALUE from YOURTABLE
    UNION
    Select PKEY, COL4 as COLVALUE from YOURTABLE) NORMALTRANSFORM

    INSERT INTO @PKEYITEMCOUNT
    (PKEY,
    ITEMCOUNT)
    SELECT PKEY,
    COUNT(*)
    FROM @TEMPNORMAL
    GROUP BY PKEY

    DELETE
    FROM YOURTABLE
    INNER JOIN
    (SELECT PKEY
    FROM @PKEYITEMCOUNT PKEYITEMCOUNT1
    INNER JOIN
    (SELECT PKEY1,
    PKEY2,
    COUNT(*) MATCHCOUNT
    FROM (SELECT DISTINCT
    TEMPNORMAL1.PKEY PKEY1,
    TEMPNORMAL2.PKEY PKEY2,
    TEMPNORMAL1.COLVALUE
    FROM @TEMPNORMAL TEMPNORMAL1
    INNER JOIN @TEMPNORMAL TEMPNORMAL2
    ON TEMPNORMAL1.COLVALUE = TEMPNORMAL2.COLVALUE
    AND TEMPNORMAL1.PKEY <> TEMPNORMAL2.PKEY) MATCHQUERY
    GROUP BY PKEY1,
    PKEY2) PKEYMATCHES
    ON PKEYITEMCOUNT.PKEY = PKEYMATCHES.PKEY
    AND PKEYITEMCOUNT.ITEMCOUNT = PKEYMATCHES.MATCHCOUNT
    INNER JOIN @PKEYITEMCOUNT PKEYITEMCOUNT2
    ON PKEYMATCHES.PKEY2 = PKEYITEMCOUNT2.PKEY
    AND PKEYITEMCOUNT1.ITEMCOUNT < PKEYITEMCOUNT2.ITEMCOUNT) SUPERFLUOUSRECORDS
    ON YOURTABLE.PKEY = SUPERFLUOUSRECORDS.PKEY

    blindman
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oops...insert statement should read like this:

    INSERT INTO @TEMPNORMAL
    (PKEY,
    COLVALUE)
    SELECT DISTINCT
    PKEY,
    COLVALUE
    FROM
    (Select PKEY, COL1 as COLVALUE from YOURTABLE where COL1 is not null
    UNION
    Select PKEY, COL2 as COLVALUE from YOURTABLE where COL1 is not null
    UNION
    Select PKEY, COL3 as COLVALUE from YOURTABLE where COL1 is not null
    UNION
    Select PKEY, COL4 as COLVALUE from YOURTABLE where COL1 is not null) NORMALTRANSFORM
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •