CODEWITHSUNDEEP
sqlpostgresqlgrailsgrails-orm
J.Srivastava
J.Srivastava

Reputation: 51

join not working in table sql query

I have two tables as purchase_bill

id   |customer_supplier |transaction_no
________________________________________
594  |99                | Y
636  |99                | Y

and purchase_bill_return

id   |customer_supplier |transaction_no  |p_id
______________________________________________
523  |99                | Y              |536
624  |99                | Y              |536

and I want the output table in the following way

id   |pr_id
___________
594  |
636  |523
636  |624

and I am using a SQL query as:

SELECT pb.ID, 
       pr.ID 
FROM   PURCHASE_BILL pb 
       LEFT JOIN PURCHASE_BILL_RETURN pr 
              ON pb.ID = pr.PURCHASE_BILL_ID 
                 AND pb.CUSTOMER_SUPPLIER_ID = pr.CUSTOMER_SUPPLIER_ID 
WHERE  pb.COMPANY_ID = 3 
       AND pr.COMPANY_ID = 3 
       AND pr.TRANSACTION_NO = 'Y' 
       AND pb.TRANSACTION_NO = 'Y' 
       AND pr.COSTOMER_SUPLIER_ID = 99;

but it is returning only two values as:

id   |pr_id

636  |523
636  |624

Upvotes: 0

Views: 69

Answers (2)

J.Srivastava
J.Srivastava

Reputation: 51

select pb.id,pr.id from purchase_bill pb left join purchase_bill_return pr on pb.id=pr.purchase_bill_id and pb.customer_supplier_id=pr.customer_supplier_id and pr.company_id = pb.company_id and pr.transaction_no = pb.transaction_no where pb.company_id =3 and pb.transaction_no = 'Y' and pr.costomer_suplier_id=99

Upvotes: 0

cf_en
cf_en

Reputation: 1661

This is because you are applying filters in the where clause to the rows on the outside of the left join. If the row is null because of the left join, then WHERE pr.company_id = 3 will be false (pr.company_id is null, not 3).

Move all of the conditions on the pr table up to the LEFT JOIN conditions instead of conditions in the WHERE clause (note I've actually joined the pr.company_id to pb.company_id, rather than putting conditions in that they are both 3, just make sure pb.company_id is 3 and that pr.company_id matches it):

select pb.id,pr.id
from purchase_bill pb left join purchase_bill_return pr on  pb.id=pr.purchase_bill_id
    and pb.customer_supplier_id=pr.customer_supplier_id and pr.company_id = pb.company_id
    and pr.transaction_no = pb.transaction_no and pr.costomer_suplier_id=99
where pb.company_id =3 and pb.transaction_no = 'Y'

Upvotes: 1

Related Questions