many into many relation in same table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

many into many relation in same table.

Author

Topic

bkbinani
Starting Member

15 Posts

Posted - 2011-11-24 : 09:04:48

Dear Expert,
This is my 1st Query. I m Poor in Englist and NOVICE in SQL.
Please forgive me if does any thing wrong.

My Problem:

I have a table having different fields among them there
are UROW_ID (uniqidentifier), UREF_ID (Uniqidentyfier), Ivch_type (int).
This table contains about 45000 records.
URow_ID is UNIQUE in the table. URef_id may present some where
in the table in URow_ID. ie: Uref_id may be in same the table in different row present in URow_id.

I wish to select those UROW_ID who are not presented in the other Rows as URef_ID.

Plz Help me.

TIA

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-24 : 10:50:28

Both of the following should work - not sure which would perform better with UniqueIdentifiers.


SELECT UROW_ID 
FROM YourTable u1
WHERE NOT EXISTS (SELECT * FROM YourTable u2 WHERE u2.UREF_ID = u1.UROW_ID);

SELECT UROW_ID 
FROM YourTable u1
LEFT JOIN YourTable u2 ON u1.UROW_ID=u2.UREF_ID
WHERE u2.UREF_ID IS NULL;

bkbinani
Starting Member

15 Posts

Posted - 2011-11-25 : 00:38:46

Thanx.

It worked.

Very Cool and Nice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources