CODEWITHSUNDEEP
sql
Sree
Sree

Reputation: 584

SQL Query for pairing terms from one table

given the first table ,i need to join this table with the same table and get the output table.Here we need to pair terms based on same seqid and the terms should be paired only from first table to second table.

Example:S1  T1  T2
        S1  T1  T3
        S1  T2  T3

this is the correct form of output but we should not get S1 T2 T1 and S1 T3 T2 once T1,T2 is already paired in the output table.

GIVEN TABLE:

   SEQID  TID                    

   S1      T1                 
   S1      T2                      
   S1      T3                      
   S2      T2                      
   S2      T3                       
   S2      T5                     
   S2      T6

OUTPUT:

 SEQID             TID       TID                
   S1              T1         T2
   S1              T1         T3               
   S1              T2         T3                
   S2              T2         T3               
   S2              T2         T5
   S2              T2         T6
   S2              T3         T5
   S2              T3         T6
   S2              T5         T6

Thanks in advance..

Upvotes: 1

Views: 1521

Answers (2)

Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

Try the following:


SELECT      a.seqid, a.tid, b.tid
FROM        [TABLE] a
INNER JOIN  [TABLE] b on a.seqid = b.seqid
WHERE       a.tid < b.tid
ORDER BY    a.seqid, a.tid

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425411

SELECT  gt1.seqid, gt1.tid, gt2.tid
FROM    giventable gt1
JOIN    giventable gt2
ON      gt2.seqid = gt1.seqid
        AND gt2.tid > gt1.tid

Upvotes: 3

Related Questions