Find Idential KeyID

  • [font="Courier New"]Script for reference..

    This is a lookalike of the actual structure.

    CREATE TABLE FindIdentical(

    KeyIDINT,

    RefIDINT )

    INSERTFindIdentical VALUES(1, 1)

    INSERTFindIdentical VALUES(1, 2)

    INSERTFindIdentical VALUES(2, 1)

    INSERTFindIdentical VALUES(2, 2)

    INSERTFindIdentical VALUES(3, 1)

    INSERTFindIdentical VALUES(4, 2)

    INSERTFindIdentical VALUES(5, 1)

    INSERTFindIdentical VALUES(5, 2)

    SELECT * FROM FindIdentical [/font]

    Explaination = I am stuck on one code where I need to find the Identical KeyID from the above table.

    An identical KeyID is one which has same set of RefID as the given KeyID. In above example expected output is KeyID 2, 5 as it has the same set of RefID. I have done it using CTE and some other way need to do in ONE SQL statement, if possible 🙂

    Thanks,

    Abhijit

    http://abhijitmore.wordpress.com/

    Abhijit - http://abhijitmore.wordpress.com

  • :blink: All those KeyIDs have the same RefIDs...?!

  • yeap its an association table between two tables.

    Abhijit - http://abhijitmore.wordpress.com

  • Abhijit More (3/12/2010)


    [font="Courier New"]In above example expected output is KeyID 2, 5 as it has the same set of RefID

    Why 2 & 5? Are you missing a table or something?

    Sorry, I don't see what you are asking for here.

  • See its simple the table structure i have mentioned here is just an association table. The query which i want write to find identical keyID is from the same table.

    For e.g

    DECLARE @KeyID INT

    SET @KeyID = 1

    Now For this KeyID there are 2 RefIDs i.e. 1 and 2

    Now I want to search the same pair for which the RefIDs are exactly 1 and 2. i.e 2 and 5

    Thanks,

    Abhijit

    Abhijit - http://abhijitmore.wordpress.com

  • ==edit i reread this and have no idea what we are after yet...sorry==

    is this a hierarchy? so 1,2 is the parent record, and anything that starts with 2 would be a child? so 2,5 is the child?

    ==original guess=====

    i see what you are after...kind of like duplicate indexes, where you have index1(col1,col2) is really a dupe of index2(col2,col1)

    the trick is to join the table with a copy of itself;

    then it's just doing the join condition, and a where statement to ignore the self joins.

    CREATE TABLE FindIdentical(

    KeyID INT,

    RefID INT )

    INSERT FindIdentical VALUES(1, 1)

    INSERT FindIdentical VALUES(1, 2)

    INSERT FindIdentical VALUES(2, 1)

    INSERT FindIdentical VALUES(2, 2)

    INSERT FindIdentical VALUES(3, 1)

    INSERT FindIdentical VALUES(4, 2)

    INSERT FindIdentical VALUES(5, 1)

    INSERT FindIdentical VALUES(5, 2)

    SELECT * FROM FindIdentical a

    inner join FindIdentical b

    on a.KeyID = b.RefID --join on the inverts of the keys relations

    and a.RefID = b.KeyID

    where a.KeyID <> b.KeyID or a.RefID <> b.RefID --ignore joins to themselves.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • to make example simpler ....

    KeyID comes from entity "Key"

    RefID comes from entity "Ref"

    Key can have mutiple Ref and Ref can be linked with multiple Key. Its M:M relationship.

    I hope now you understand the concept instead of refering the actual values.

    Abhijit - http://abhijitmore.wordpress.com

  • Abhijit More (3/12/2010)


    Key can have mutiple Ref and Ref can be linked with multiple Key.

    So why didn't you say it is a bridge table in your original question?

    A simple example of the data in the other tables, and the output expected would have made it much clearer.

    As it stands, all the rows link to the same rows in both of the bridged tables...so I still don't get it...

    Abhijit More (3/12/2010)


    I hope now you understand the concept instead of refering the actual values.

    So we need to change the values in your example too? :blink:

  • I guess I named it as an associative table between two tables. The values are correct.

    Abhijit - http://abhijitmore.wordpress.com

  • Abhijit More (3/15/2010)


    I guess I named it as an associative table between two tables. The values are correct.

    In your second post, yes. That's why I asked if there was a table definition missing.

    What is the basis for deciding that keys 2 and 5 should be returned please?

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

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