CODEWITHSUNDEEP
sqlpostgresql
Denny
Denny

Reputation: 1759

SQL Select From 2 different tables

I am trying to select data from two tables but I can't figure out the correct way. I got 2 tables - loans and requests. Loans has fields which are of interest to this particular query e.g status, loan_id. On the other hand, the requests table has some fields too. My query is trying to get data from these 2 tables as such:

SELECT a.request_id,
    a.request_date,
    a.customer_id,
    a.request_type,
    a.target_customer,
    a.customer_id,
    a.agent_number,
    a.merchant_number,
    a.bill_reference,
    d.loan_id,
    a.amount
   FROM loans d,
    requests a
    WHERE d.loan_status=12

This returns a result with the loan_id field with the same value in each record. PS: This loan_id column can be null since not all requests are loan applications.

Upvotes: 0

Views: 71

Answers (2)

geoandri
geoandri

Reputation: 2428

You should have a field in the request table (for example loan_id) which references to the loan_id in the loan table. In this case you 'll have to use the following query

SELECT a.request_id,
a.request_date,
a.customer_id,
a.request_type,
a.target_customer,
a.customer_id,
a.agent_number,
a.merchant_number,
a.bill_reference,
d.loan_id,
a.amount
 FROM requests a left join  loans d on a.loan_id=d.loan_id
  where d.loan_status=12

Upvotes: 2

pradeep varma
pradeep varma

Reputation: 136

You Have to use Join like

SELECT a.request_id,
a.request_date,
a.customer_id,
a.request_type,
a.target_customer,
a.customer_id,
a.agent_number,
a.merchant_number,
a.bill_reference,
d.loan_id,
a.amount
 FROM loans d inner join
requests a
on d.loan_status=12

Upvotes: 0

Related Questions