CODEWITHSUNDEEP
sqlsql-server
jcvegan
jcvegan

Reputation: 3170

SQL select column equivalence

I have a table that saves the possible states of other tables (entities). But now I need to find equivalence of states between two entities. The table structure is something like this

ID   TableID   StateValue   StateDefinition  StateDescription
================================================================
1    1         1            Created          Just created
2    1         2            Dropped          Just Dropped
3    2         1            Created          Just Created
4    2         2            Aproved          Passed the revision
5    2         3            Dropped          Just dropped

I want to get equivalent (comparing text of state) which as a result get this:

   TableID1     StateValue1       TableID2       StateValue2    StateDefinition
   =============================================================================
   1            1                 2              1              Created
   1            2                 2              3              Dropped

My question is, how can it be done??

Upvotes: 0

Views: 120

Answers (2)

N West
N West

Reputation: 6819

Do a self join on the table.

A general case might look like:

SELECT A.TableID as TableId1, 
       A.StateValue as StateValue1, 
       B.TableId as TableId2, 
       B.StateValue as StateValue2,
       A.StateDefinition
FROM
  Table A 
INNER JOIN Table B 
  ON (A.TableId <> B.TableId and A.StateDefiniton = B.StateDefinition)

Upvotes: 3

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171509

select t1.TableID as TableID1,
    t1.StateValue as StateValue1,
    t2.TableID as TableID2,
    t2.StateValue as StateValue2,
    t1.StateDefinition  
from MyTable t1
inner join MyTable t2 on t1.TableID = 1 and t2.TableID = 2
where t1.StateValue = t2.StateValue 
    and t1.StateDefinition  = t2.StateDefinition

Upvotes: 2

Related Questions