CODEWITHSUNDEEP
sqlpostgresql
rolz
rolz

Reputation: 69

How can I join 2 sub queries in postgresql?

I have 4 tables : T1,T2,T3,T4

--T1:               
    Info_Id | fname | lname
    101     | rol   | lum 

--T2:
    Acc_id | acc_info_id | acc_property
    01     | 101         |  land       

--T3:
    Bill_id | status    | amt_2_paid |  Acc_id
    123     |  UNPAID   |  200       | 01   

--T4:  
    Accnt_bill | OR      |  Acc_id
    123        | 01234   |  01

I have two sub queries, shown below:

SELECT 
      fname,
      lname 
  FROM T1 
 WHERE Info_id IN(SELECT Acc_id FROM T2 WHERE acc_info_id = '01234')

The first sub query displays only fname and lname:

SELECT 
      status,
      amt_2_paid 
 FROM T3 
WHERE Bill_id IN(SELECT Accnt_id FROM t4 WHERE OR = '01234')

The second subquery displays only status and amt_2_paid:

My question is how to join two sub queries in order to display the value of fname, name, status, and amt_2_paid when I input the OR no.

Upvotes: 1

Views: 1133

Answers (3)

kjanz1899
kjanz1899

Reputation: 47

SELECT 
    t1.fname,
    t1.lname,
    t3.status,
    t3.amt_2_paid
FROM 
    t1 
    INNER JOIN t2 on t1.info_id = t2.acc_info_id
    INNER JOIN t3 on t2.acc_id = t3.acc_id
    INNER JOIN t4 on t3.bill_id = t4.accnt_bill
WHERE 
    t4.[or] = '01234'

Upvotes: 0

Rigel1121
Rigel1121

Reputation: 2016

Just combine all your tables with one query using INNER JOIN. See below:

SELECT 
      T1.fname, 
      T1.lname, 
      T3.status, 
      T3.amt_2_paid
FROM T1 
INNER JOIN T2 ON T1.Info_id=T2.acc_info_id
INNER JOIN T3 ON T2.Acc_id=T3.Acc_id
INNER JOIN T4 ON T3. Bill_id=T4.Accnt_bill
WHERE T4.[OR] ='01234'

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

 SELECT fname, name, status,amt_2_paid 
 FROM T3 
 INNER JOIN t4 ON t3.Bill_id = t4.Accnt_bill
 INNER JOIN t2 ON t2.Acc_id = t3.Acc_id
 INNER JOIN t1 ON t1.Info_id = t2.Info_Id
 WHERE t4."OR" = '01234'

Upvotes: 0

Related Questions