Help with complicated script

[james0816]

or at least for me. I have two tables (a and b). table a houses unique records (i.e. pricing information). table b houses invoicing information). here's the tricky part.

I am trying to create a report which will list all the items including what has been invoiced. I have been able to get the join down (or so i thought) but i am getting duplicate records since table two contains at times two entries for one item in table a.

Table A

NUMBER LINEITEM COST
100 1 1000

Table B

NUMBER PNUMBER INVCOST
1 100 600
2 100 400

So my report looks like this:

Purchasing Details:

NUMBER LINEITEM COST INVCOST
100 1 1000 600
100 1 1000 400

Totals:
Total Purchased Price: 2000 Total Invoiced Price: 1000

How do I go about correcting the double records?

Please let me know if you need more information.

thx

 

[Bastien]

add grouping to the invcost field

select a.number, a.cost, sum(b.invcost) from
tableA a,
tableB b
where a.number = b.pnumber
group by b.pnumber


something like that


Bastien

Cat, the other other white meat