Find Idential KeyID

Question

Post reply

Find Idential KeyID

Abhijit More

SSCertifiable

Points: 6897
More actions
March 12, 2010 at 4:00 am

#221721

[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

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

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

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

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Abhijit More SSCertifiable Points: 6897 More actions · Answer 2

yeap its an association table between two tables.

Abhijit - http://abhijitmore.wordpress.com

Paul White SSC Guru Points: 150468 More actions · Answer 3

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.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Abhijit More SSCertifiable Points: 6897 More actions · Answer 4

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

Lowell SSC Guru Points: 323547 More actions · Answer 5

==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!

Abhijit More SSCertifiable Points: 6897 More actions · Answer 6

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

Paul White SSC Guru Points: 150468 More actions · Answer 7

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:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Abhijit More SSCertifiable Points: 6897 More actions · Answer 8

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

Abhijit - http://abhijitmore.wordpress.com

Paul White SSC Guru Points: 150468 More actions · Answer 9

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?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi