There are situations where you may have to rethink queries to tackle complex problems. Here is one of them. We had a table in a SQL Server database called NAMELINK where no primary key was defined. The structure was as follows:
NameId1 int 4 not null Category char 5 nullable Relation12 char 20 nullable NameId2 int 4 not null Relation21 char 21 nullable Remarks char 30 nullable Dependent smallint 2 not nullWhile converting to another database, we assumed that NameId1 and NameId2 are composite primary keys. There may be some cases where they aren't unique. Now we wanted a select query to fetch only those records where the combination of NameId1 and NameId2 is unique.
To be more clear, if the values are as follows:
Nameid1 Nameid2 1 2 2 1Then the select query should fetch only one combination and not both.
Here is the query. It has been tested on SQL Server 7.0:
SELECT NAMELINK.* FROM NAMELINK LEFT JOIN (SELECT nl1.* FROM NAMELINK nl1 INNER JOIN NAMELINK nl2 on nl1.Nameid2 = nl2.Nameid1 and nl1.Nameid1 = nl2.Nameid2 ) MyNameLink ON Namelink.Nameid1 = MyNameLink.Nameid1 and Namelink.Nameid2 = MyNameLink.Nameid2 WHERE MyNameLink.Nameid1 IS NULL AND MyNameLink.Nameid2 IS NULL UNION ALL SELECT nl2.* FROM NAMELINK nl1 INNER JOIN NAMELINK nl2 on nl1.Nameid2 = nl2.Nameid1 and nl1.Nameid1= nl2.Nameid2 WHERE nl2.Nameid1 = ( SELECT TOP 1 nl3.Nameid1 FROM NAMELINK nl3 INNER JOIN NAMELINK nl4 ON nl3.Nameid2 = nl4.nameid1 and nl3.Nameid1 = nl4.Nameid2 WHERE (nl3.Nameid1 + nl3.Nameid2) = (nl1.Nameid1 + nl1.Nameid2) ) and nl2.Nameid2 = ( SELECT TOP 1 nl3.Nameid2 FROM NAMELINK nl3 INNER JOIN NAMELINK nl4 ON nl3.Nameid2 = nl4.nameid1 and nl3.Nameid1 = nl4.Nameid2 WHERE (nl3.Nameid1 + nl3.Nameid2) = (nl1.Nameid1 + nl1.Nameid2))
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free SQL Server tips and scripts.
- Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
- Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.
This was first published in December 2002