CODEWITHSUNDEEP
sqlpostgresql
user3614504
user3614504

Reputation: 35

how to view the name of credit and debit in same table from other table

I have to tables, table accounts and table entities, the credit and debit are number = id in table account, i want to write a query and the result is the name of credit number and the name of debit number and date and value from table entities

some data from tables:

table entities:    
id  credit   debit  value  date
1   100      101    5000   01/01/2014

table accounts:    
id   name
100  Mark
101  Jone

this some data from my database. All what I need a query to show this result:

1, Mark, Jone, 5000, 01/01/2014

Upvotes: 0

Views: 931

Answers (1)

jpw
jpw

Reputation: 44881

You'll need to join the accounts table twice (once for the credit name, and once for the debit name):

SELECT
    e.id, 
    credit_account.name AS CreditName, 
    debit_account.name  AS DebitName, 
    e.value, 
    e.date 
FROM entities AS e
JOIN accounts AS credit_account ON e.credit = credit_account.id
JOIN accounts AS debit_account  ON e.debit  = debit_account.id

This query produces this output:

id          CreditName           DebitName            value       date
----------- -------------------- -------------------- ----------- ----------
1           Mark                 Jone                 5000        2014-01-01

It assumes there is a value in both the credit and debit columns for each row in the entities table. If one of them can be missing you might want to useLEFT JOINinstead to get a null value in the column with the missing value.

Sample SQL Fiddle

Upvotes: 1

Related Questions