CODEWITHSUNDEEP
sqlsql-serverselectinner-join
Andrey
Andrey

Reputation: 1759

select from same table but different columns

I have table whic looks like

from_type, from_id, to_type, to_id A 12 B 15 B 15 C 16 I tryed 

select  A.FROM_TYPE,A. FROM_ID, B.TO_TYPE, B.TO_ID 
from  ITEM_LINK A   JOIN( select FROM_TYPE, FROM_ID, TO_TYPE, TO_ID 
                               from table
                              WHERE FROM_TYPE = 'B' and TO_ITEM_TYPE ='C')
  B ON A.TO_TYPE =B.FROM_TYPE
  where A.FROM_TYPE = 'A' and A.TO_TYPE ='B'

however this query doesn't work right , As an answer from this query i want to have

from_type, from_id, to_type, to_id A 12 C 16 I don't need to have B in answer. also this query will be join by FROM_ID FROM_TYPE A in another query. Any ideas how to build this query?

My answer is not right because its showing me something like this

from_type, from_id, to_type, to_id A 12 C 16 A 12 C 17 A 12 C 18 which is not right because TYPE A can connect to only one TYPE C

Upvotes: 0

Views: 55

Answers (1)

echo_Me
echo_Me

Reputation: 37253

Try this

  select t1.`from_type`, t1.`from_id`, t2.`to_type`, t2.`to_id` from Table1 t1
   inner join table1 t2
   On t1.`to_type` = t2.`from_type`
   and t1.`to_id` = t2.`from_id`

DEMO HERE

sorry didnt see its sql server

try that

  select t1.[from_type], t1.[from_id], t2.[to_type], t2.[to_id] from Table1 t1
  inner join table1 t2
  On t1.[to_type] = t2.[from_type]
  and t1.[to_id] = t2.[from_id]

DEMO for sqlserver

Upvotes: 1

Related Questions