CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Mohammad Yusuf
Mohammad Yusuf

Reputation: 17054

Two joins on single table

I have 2 tables:

Table1
id    acc    offacc    debit    credit
1     43     44        12.2     0

Table2
id    dispval
43    ub01
44    cust02

How can I get this in Single Row:

id    acc    offacc    debit    credit
1     ub01   cust02    12.2     0

Upvotes: 0

Views: 36

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37337

Alternative to join:

select id,
       (select dispval from Table2 where id = [t1].acc) as acc,
       (select dispval from Table2 where id = [t1].offacc) as offacc,
       debit,
       credit
from Table1 [t1]

Upvotes: 2

DhruvJoshi
DhruvJoshi

Reputation: 17126

Consider using LEFT JOIN twice on the table1 like below

select 
t1.id,
acc=t2.dispval,
offacc=t3.dispval,
t1.debit,
t1.credit
from Table1 t1 left join
     Table2 t2 on t1.acc=t2.id
     Table2 t3 on t1.offacc=t3.id

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

use table alias

select t1.id, t2a.dispval as acc, t2b.dispval as offacc, t1.debit, t1.credit
from   Table1 as t1
       inner join Table2 as t2a on t2a.id = t1.acc
       inner join Table2 as t2b on t2b.id = t1.offacc

Upvotes: 4

Related Questions