March 12, 2010 at 4:00 am
[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
March 12, 2010 at 4:44 am
:blink: All those KeyIDs have the same RefIDs...?!
March 12, 2010 at 5:12 am
yeap its an association table between two tables.
Abhijit - http://abhijitmore.wordpress.com
March 12, 2010 at 5:26 am
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.
March 12, 2010 at 6:47 am
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
March 12, 2010 at 7:08 am
==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
March 12, 2010 at 7:54 am
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
March 12, 2010 at 6:53 pm
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:
March 15, 2010 at 12:41 am
I guess I named it as an associative table between two tables. The values are correct.
Abhijit - http://abhijitmore.wordpress.com
March 15, 2010 at 1:08 am
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