Page 1 of 1

sql join and display help

Posted: Fri Mar 17, 2006 9:03 am
by gurjit
Hi,

I have three tables

Code: Select all

tbl_invoice_number
invnoid, int(11), primary
invno_date, date


tbl_invoice_payment
invid, int(11), primary
ivp_date_in, date
inv_payment_amount, float
frn_invnoid, int(11) // the_invoice_number which the payment belongs to

tbl_invoice_item 
iviid, int(11), primary
ivi_date_in, date 
ivi_unit_price, float // price of the product
ivi_product_code, varchar(100) // name of product brought
frn_invnoid, int(11) // the_invoice_number which the payment belongs to
What I want to do is when I pass through the "invnoid", I want to bring all the payments and items relating to this invnoid.

The output must come in date order.....

Any ideas how I can achieve this?????

Posted: Fri Mar 17, 2006 9:41 am
by ol4pr0

Re: sql join and display help

Posted: Fri Mar 17, 2006 10:03 am
by RobertGonzalez
gurjit wrote:

Code: Select all

tbl_invoice_number
invnoid, int(11), primary
invno_date, date


tbl_invoice_payment
invid, int(11), primary
ivp_date_in, date
inv_payment_amount, float
frn_invnoid, int(11) // the_invoice_number which the payment belongs to

tbl_invoice_item 
iviid, int(11), primary
ivi_date_in, date 
ivi_unit_price, float // price of the product
ivi_product_code, varchar(100) // name of product brought
frn_invnoid, int(11) // the_invoice_number which the payment belongs to

Code: Select all

<?php
$sql = "SELECT n.*, p.*, i.* 
        FROM tbl_invoice_item i 
            INNER JOIN tbl_invoice_payment p 
                ON i.frn_invnoid = p.frn_invnoid
        INNER JOIN tbl_invoice_number n 
            ON p.frn_invnoid = n.invnoid 
        WHERE n.invnoid = $user_supplied_invoice_number";
?>
Will this work?

Posted: Fri Mar 17, 2006 10:57 am
by gurjit
only brings out the invoice with a payment in tbl_invoice_payment and also brings the same inv_payment_amount fpr each record.

anyway of splitting the payments. I only want to pull out

inv_payment_amount and ivi_unit_price

as individual records

e.g.
ivi_unit_price, ivp_amount_paid
220.00,0.00
210.00,0.00
0.00,500.00

Posted: Fri Mar 17, 2006 11:17 am
by RobertGonzalez

Code: Select all

<?php
$sql = "SELECT p.inv_payment_amount, i.ivi_unit_price 
        FROM tbl_invoice_item i 
            INNER JOIN tbl_invoice_payment p 
                ON i.frn_invnoid = p.frn_invnoid
        WHERE i.frn_invnoid = $user_supplied_invoice_number";
?>

Posted: Mon Mar 20, 2006 3:07 am
by gurjit
This works well, if a "inv_payment_amount" exists but if an invoice does not have a "inv_payment_amount" made to it then the items should still come out.....

do i need to use a outer join?

Posted: Tue Mar 21, 2006 6:45 am
by gurjit
I have been trying but no luck anyone have an answer....

i tried all the joins

what I want is a display like this:

ivi_unit_price, ivp_amount_paid
220.00,0.00
210.00,0.00
0.00,500.00


if no ivi_unit_price OR ivp_amount_paid exist then a 0.00 get display.

There will always be a ivi_unit_price but not necessarly any ivp_amount_paid.

This is because a line item exists but no payments may have been made

Posted: Tue Mar 21, 2006 8:14 am
by RobertGonzalez
gurjit wrote:This works well, if a "inv_payment_amount" exists but if an invoice does not have a "inv_payment_amount" made to it then the items should still come out.....

do i need to use a outer join?
I don't think this is going to happen. Since you are using a JOIN and a WHERE based on an invoice number, the database will return all JOINED records where the supplied invoice number statisfies the query based on the join. You are not going to be able to pull from two tables that are joined on a field where one of the fields returns empty.

Posted: Tue Mar 21, 2006 8:20 am
by gurjit
I tried this but it always brings out

ivi_unit_price, ivp_amount_paid
220.00,500.00
210.00,500.00
220.00,500.00

There is one payment of 500.00 in tbl_invoice_payment and 3 invoice items in tbl_invoice_item, for each item it brings the same ivp_amount_paid (500.00).....

Code: Select all

select i.ivi_unit_price,  p.ivp_amount_paid
from tbl_invoice_item i left join tbl_invoice_payment p
on i.frn_invnoid = p.frn_invnoid
WHERE i.frn_invnoid = 17 AND i.ivi_status = 1

What other method of bringing out the data would you suggest?

What i'm trying to do is produce an invoice with the unit price and payments made for the invoice and want to order the unit price and payment in date order........

should I use an array, put all the information in an array and then sort using php?
what array should I use??? I will be outputting something like this on invoice

Item name, credit, debit, balance
basinstoke 1, 220.00,,220.00
0001 transaction,,100.00,120.00
basingstoke 2,120.00,,240.00

Posted: Tue Mar 21, 2006 8:23 am
by RobertGonzalez
That's my typical way of doing it. If it is not some outrageously large result set then I just grab the data that is closest to what I need and use my code to make it pretty.

Something to try... write one query to grab the invoice items. Write another query to grab the payments. Match them up code-side. See if it works.

Posted: Tue Mar 21, 2006 8:26 am
by gurjit
what type of array would you use to output the result I have described in the post before your last post?

Posted: Tue Mar 21, 2006 10:55 am
by RobertGonzalez
I think your database table structure might need some work. As I was writing a small piece of code to loop through the values I noticed something...

There is nothing that ties your items table and payment table together except the invoice ID. This means that payments will be applied to an invoice, not an item. So to get what you want, you are going to have to modify your item table and/or payment table by adding a key for the two to join by. This is the only way you will be able to see payments against an item rather than an entire invoice.

Posted: Thu Mar 23, 2006 3:07 am
by gurjit
Hi,

the idea is to make a payment against an invoice and not a item.

A invoice will have many items
A invoice will have many payments
The payment will be made against an invoice.

So for example the display I WANT is as below in date order and mix records:
Invoice number: 0001 // this comes from tbl_invoice_number

Date,Item, unit price, debit, credit, total

19/03/2006,basingstoke 1, 220.00, 220.00,0.00, 220.00 // this comes from tbl_invoice_item

19/03/2006,payment,0.00,0.00,10.00,210.00 // this comes from tbl_invoice_payment

20/03/2006,basingstoke 2, 220.00, 220.00,0.00, 430.00 // this comes from tbl_invoice_item

21/03/2006,payment,0.00,0.00,30.00,400.00 // this comes from tbl_invoice_payment
the tables "tbl_invoice_item" and "tbl_invoice_payment" both have a date_in field which is the date, the record was inserted. I want to display the list in date order as above.

Thats the whole reason for writing this query, otherwise I could have output the tbl_invoice_item first and tbl_invoice_payment afterwards and the display would then be like:


THIS IS WHAT I DON'T WANT:
Invoice number: 0001 // this comes from tbl_invoice_number

Date,Item, unit price, debit, credit, total

19/03/2006,basingstoke 1, 220.00, 220.00,0.00, 220.00 // this comes from tbl_invoice_item

20/03/2006,basingstoke 2, 220.00, 220.00,0.00, 440.00 // this comes from tbl_invoice_item

19/03/2006,payment,0.00,0.00,10.00,430.00 // this comes from tbl_invoice_payment

21/03/2006,payment,0.00,0.00,30.00,400.00 // this comes from tbl_invoice_payment