CODEWITHSUNDEEP
sqlms-access
sneha khan
sneha khan

Reputation: 69

sql query joining two columns with one

I have 2 tables in ms access 2010 as below 

USERS (u_id, u_name)
LOAN (l_id, l_from[ref users.u_id], l_to[ref users.u_id], l_amount)

Users
+------+--------+
| u_id | u_name |
+------+--------+ 
| 1    | abc    |
| 2    | def    |
+------+--------+

Loan
+-----+--------+------+----------+
|l_id | l_from | l_to | l_amount |
+-----+--------+------+----------+
| 1   | 2      | 1    | 100      |
| 2   | 2      | 1    | 100      |
| 3   | 1      | 1    | 50       |
+-----+--------+------+----------+

I want to select names instead of numbers(l_from & l_to) from loan table

select
  loan.l_id,
  loan.l_from,
  users.u_name user_from,
  loan.l_to,
  users.u_name as user_to,
  loan.l_amount
from loan, users
where ???

Upvotes: 1

Views: 781

Answers (2)

lc.
lc.

Reputation: 116458

Just join on the Users table twice, once for each of from and to.

SELECT l.l_id, uFrom.u_name AS user_from,
    uTo.u_name AS user_to, l.l_amount
FROM LOAN l
INNER JOIN Users uFrom ON l.l_from = uFrom.u_id
INNER JOIN Users uTo ON l.l_to = uTo.u_id

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

JOIN the users table two times like so:

SELECT
  l.l_id,
  fromusers.u_name AS user_from,
  tousers.u_name AS user_to,
  l.l_amount
FROM loan l
INNER JOIN users fromusers ON l.l_from = fromusers.u_id
INNER JOIN users tousers ON l.l_to = tousers.u_id

SQL Fiddle Demo (it is SQL Server 2008, but should be the same syntax for ms-access, I think)

Upvotes: 3

Related Questions