1

I have three tables

member

  • id
  • name
  • address

tax_bill

  • bill_id
  • member_id
  • tax_amount

tax_receipt

  • receipt_id
  • member_id
  • paid_amount

Out put:

Name | Address | Tax | Paid Tax
ABC  | xyz     | 100 |100
PQR  | yte     | 200 | Not paid

here "not paid" is the default value which is not there in receipt table the record is only in member and bill table.

From above tables i want to show the member details with his bill detail and receipt details. Initially the tax_receipt will be empty, When the user will pay the tax, the entry will be in the tax_receipt detail. I want to create a report in which all the members detail from member table, tax detail from tax_bill and receipt detail from tax_receipt will be displayed. What i want is to show ZERO if the record is not found in tax_receipt table for perticular member's tax_bill details.

Also, i am using join query so it will be usefull if suggestion will be using join query.

Thank you in advance :)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
NaikNiket
  • 29
  • 1
  • 5
  • This looks like a left join and using ISNULL() for the rows that have no matching values. Can you edit your question to include your query? – Rigerta Jul 26 '18 at 08:31
  • Actually i have more then these tables and the query is much longer then this and i am just stucked at this point only, so what i will do is from the suggestions i will implement in my query. Yet, this is the query i am using. https://shrib.com/#4VxNgMBH8nO9rbVny9ew – NaikNiket Jul 26 '18 at 08:35

2 Answers2

1

You seems want left outer join :

select m.Name, m.Address, txb.tax_amount as Tax, 
      isnull(cast(txr.paid_amount as varchar(255)), 'Not paid') as PaidTax 
from member m inner join
     tax_bill txb
     on txb.member_id = m.id left outer join
     tax_receipt txr
     on txr.receipt_id = txb.bill_id;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
1

This is what you need (considering the example tables you provided): 

select m.name, m.address, t.tax_amount, case when isnull(r.paid_amount, 0) = 0 then 'not paid' else cast(r.paid_amount as varchar(100)) end as paid_tax
from member m join tax_bill t on m.id = t.member_id
              left join tax_receipt r on m.id = r.member_id

There's a working copy here.

However, for your complicated long query mentioned in the comments, you should first format it so that it is legible, then read about the importance of the join order when using left-right-full joins to join in the correct order for your needs and then try removing joins one by one to find out where your missing record gets lost.

Good luck!

Rigerta
  • 3,959
  • 15
  • 26