Only return values on first row of join
Table A is ORDERS. In this, there is the OrderNo and other columns such as: total cost, total tax, total discount, total shipping.
Table B is ITEMS. In this there is order# and rows for each item on an order (1 or more per order).
The problem:
I want my output to only show the total cost, total tax, total discount, total shipping on the first row of each order (there may be multiple rows due to multiple items).
As it is today shown below, but gives "orderDate, customer#, total cost, total tax, total discount, total shipping" on every row. I only need this once per orderNo. I'd like those fields to be blank on subsequent rows within each order.
I'm using DB2 for i, but I'm hopeful I could adapt most any SQL query regardless of specific SQL implementation.
Table B is ITEMS. In this there is order# and rows for each item on an order (1 or more per order).
The problem:
I want my output to only show the total cost, total tax, total discount, total shipping on the first row of each order (there may be multiple rows due to multiple items).
As it is today shown below, but gives "orderDate, customer#, total cost, total tax, total discount, total shipping" on every row. I only need this once per orderNo. I'd like those fields to be blank on subsequent rows within each order.
select tableA.OrderNO, orderDate, customer#, total cost, total tax, total discount, total shipping
,tableB.Item#, ItemDescription, ItemCost, ItemSalesPrice
from tableA
join tableB on tableA.orderno = tableB.orderno
,tableB.Item#, ItemDescription, ItemCost, ItemSalesPrice
from tableA
join tableB on tableA.orderno = tableB.orderno
I'm using DB2 for i, but I'm hopeful I could adapt most any SQL query regardless of specific SQL implementation.
ASKER
The "blanks" would be in the "amounts". Imaging trying to get totals when the order total, sales tax, discount is repeated on every row :-)
For better or worse all of those things are applied at the order level, not item. Yet I still need a listing of the items with the order output.
I don't disagree with your assessment, although I'd much prefer to "spit it out" in it's final form since manual manipulation is quite messy.
For better or worse all of those things are applied at the order level, not item. Yet I still need a listing of the items with the order output.
I don't disagree with your assessment, although I'd much prefer to "spit it out" in it's final form since manual manipulation is quite messy.
can you provide some sample data and expected output
Hi!
I would do two queries for this. One for the total cost and one to list the items.
You should skip the columns customer# from the latter query as I put it only there for you to verify that the data in both queries matches.
I assume that you know the customerno and orderdate in the where clause I use and I use the orderno from the first query in the latter.
Regards,
Tomas Helgi
I would do two queries for this. One for the total cost and one to list the items.
select tableA.OrderNO, orderDate, customer#, total cost, total tax, total discount, total shipping
from tableA
where customerno = ? and orderdate = select tableA.OrderNO, orderDate, customer#,
,tableB.Item#, ItemDescription, ItemCost, ItemSalesPrice
from tableA
join tableB on tableA.orderno = tableB.orderno
where tableA.orderno = ? and customerno = ? You should skip the columns customer# from the latter query as I put it only there for you to verify that the data in both queries matches.
I assume that you know the customerno and orderdate in the where clause I use and I use the orderno from the first query in the latter.
Regards,
Tomas Helgi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I swear Rex read my mind! Exactly what I was looking to do. Thank you very much!
I appreciate everyone's input!
I appreciate everyone's input!
Just imagine the nightmare if trying to sort the output by order number if most rows have a blank in that column.