CODEWITHSUNDEEP
sql
Ethan Chen
Ethan Chen

Reputation: 13

Join table and applying filter?

I have a question on this query forever and never figured it out, need some help and thanks in advance for all answers! I have table 1: Payment (storing historical payment info, I only look at one month data and only type#1 payment, so i need to apply two filters here)

Pymt 

ServiceID InsuranceID Amount Month Type
1         A           $10    Jun15 1
2         A           $15    Jun15 1
3         B           $15    Jun15 1
4         C           $30    Jun15 1
5         D           $50    Jun15 1

Ins

Insurance ID    Name
A               AAA
B               BBB
C               CCC
D               DDD

Expected Result

Service ID  Insurance ID    Name    Amount  Month   Type
1           A               AAA     $10      Jun15   1
2           A               AAA     $15      Jun15   1
3           B               BBB     $15      Jun15   1
4           C               CCC     $30      Jun15   1
5           D               DDD     $50      Jun15   1

So there are two tasks here:
1. filter table 1 by Jun 15 and Type 1

  1. add a new column"Name" in table 1 to indicate the name of the insurance WITHOUT adding new rows ( it is like a vlookup ) so monthly amount total is the same

I tried differnt joins and WHERE(filter) always added new rows to result which I don't want, please advise.

Thanks!

Upvotes: 1

Views: 56

Answers (1)

user5135401
user5135401

Reputation: 218

select p.ServiceID, i.InsuraneID, i.Name, p.Amount, p.Month, p.Type
from Pymt p
inner join Ins i on p.InsuranceID = i.InsuranceID
where p.Month='Jun15' and p.Type=1

Upvotes: 1

Related Questions