Not signed in (Sign In)

Vanilla 1.1.4 is a product of Lussumo. More Information: Documentation, Community Support.


Archive: Duplicate rows

Bottom of Page

1 to 4 of 4

  1.  
    • CommentAuthoredgar
    • CommentTimeNov 6th 2006
     
    Hi
    I having a few problems what I need to do is find duplicate rows in a mssql database I can do from one table but I need it to run across 2 tables
    this is the code I using to do one table
    SELECT COUNT(*), sID1, sID2, sID3 FROM ITEM_T
    WHERE (sItemID = 760) OR
    (sItemID = 761) OR
    (sItemID = 762)
    GROUP BY sID1, sID2, sID3
    HAVING COUNT(*)>1;

    this is the other table I need to include

    SELECT COUNT(*), sID1, sID2, sID3 FROM BANKITEM_T

    the cloums within the tables are the same

    Many thanks
    • CommentAuthorbraden
    • CommentTimeNov 6th 2006
     
    Ok you need also to read about the relational model (just google those terms and look for F Codd) as well as SQL syntax. Duplicates should NEVER be allowed, and databases designed with duplicate rows are bad designs and are exactly the cause of difficult query design you are now having to come up with. That's some preventative medicine for you to consider in the future when you are fully fledged database developer/administrator.

    Now I'm not too sure what you are asking for. Are you saying sItemID 760 - 762 are allowed to be duplicated and ALL other sItemID's are not ?

    So you want to select all duplicated sItemID's that AREN'T 760,761 or 762 ?
    • CommentAuthorjustnajm
    • CommentTimeNov 14th 2006
     
    Any relation should be there between both tables thats how I tries to pick the data from two tables....

    This makes the database much simple to control and retreive data from them on the basis of their relations.
    • CommentAuthorspydev
    • CommentTimeNov 14th 2006
     
    well of corse the person above is right. You need to create relation for the same data/columns in different tables..

    But for you original implementation try this...
    SELECT COUNT(I.*), COUNT(B.*), I.sID1, I.sID2, I.sID3, B.sID1, B.sID2, B.sID3
    FROM ITEM_T I, BANKITEM_T B
    WHERE (I.sItemID = 760) OR
    (I.sItemID = 761) OR
    (I.sItemID = 762)
    GROUP BY I.sID1, I.sID2, I.sID3
    HAVING COUNT(I.*)>1 And COUNT(B.*)>1;

    I hope this will work for your current situation but its very bad implementation.. Good is to use foreign keys to relate the data

1 to 4 of 4

  1.