CODEWITHSUNDEEP
sqlinner-join
Nikhil Rupanawar
Nikhil Rupanawar

Reputation: 4191

SQL Inner Join query

I have following table structures,

cust_info 

   cust_id
   cust_name

bill_info

    bill_id
    cust_id
    bill_amount
    bill_date

paid_info

    paid_id
    bill_id
    paid_amount  
    paid_date

Now my output should display records (1 jan 2013 to 1 feb 2013) between two bill_dates dates as single row as follows,

 cust_name | bill_id | bill_amount | tpaid_amount | bill_date | balance

where tpaid_amount is total paid for particular bill_id

For example,

so, tpaid_amount = 2000 + 3000 = 5000 and balance = 10000 - tpaid_amount = 10000 - 5000 = 5000

Is there any way to do this with single query (inner joins)?

Upvotes: 0

Views: 134

Answers (2)

Craig Brett
Craig Brett

Reputation: 2340

You'd want to join the 3 tables, then group them by bill ids and other relevant data, like so.

-- the select line, as well as getting your columns to display, is where you'll work 
-- out your computed columns, or what are called aggregate functions, such as tpaid and balance
SELECT c.cust_name, p.bill_id, b.bill_amount, SUM(p.paid_amount) AS tpaid, b.bill_date, b.bill_amount - SUM(p.paid_amount) AS balance
-- joining up the 3 tables here on the id columns that point to the other tables
FROM cust_info c INNER JOIN bill_info b ON c.cust_id = b.cust_id
INNER JOIN paid_info p ON p.bill_id = b.bill_id
-- between pretty much does what it says
WHERE b.bill_date BETWEEN '2013-01-01' AND '2013-02-01'
-- in group by, we not only need to join rows together based on which bill they're for
-- (bill_id), but also any column we want to select in SELECT. 
GROUP BY c.cust_name, p.bill_id, b.bill_amount, b.bill_date

A quick overview of group by: It will take your result set and smoosh rows together, based on where they have the same data in the columns you give it. Since each bill will have the same customer name, amount, date, etc, we are fine to group by those as well as the bill id, and we'll get a record for each bill. If we wanted to group it by p.paid_amount, though, since each payment would have a different one of those (possibly), you'd get a record for each payment as opposed to for each bill, which isn't what you'd want. Once group by has smooshed these rows together, you can run aggregate functions such as SUM(column). In this example, SUM(p.paid_amount) totals up all the payments that have that bill_id to work out how much has been paid. For more information, please look at W3Schools chapter on group by in their SQL tutorials.

Hope I've understood this correctly and that this helps you.

Upvotes: 1

Levent Yıldız
Levent Yıldız

Reputation: 335

This will do the trick;

select
    cust_name,
    bill_id,
    bill_amount,
    sum(paid_amount),
    bill_date,
    bill_amount - sum(paid_amount)
from
    cust_info
    left outer join bill_info
        left outer join paid_info
        on bill_info.bill_id=paid_info.bill_id
    on cust_info.cust_id=bill_info.cust_id
where
    bill_info.bill_date between X and Y
group by
    cust_name,
    bill_id,
    bill_amount,
    bill_date

Upvotes: 1

Related Questions