October 18, 2013 at 7:01 pm
I am trying to pull out the vendor name and how much they owe. I am not getting the results I am looking for.
Vendor table:(vendor_id, vendor_name)
1, Ace
2, Bond
3, Computer
4 , House
Order holds: (ord_id, vender_id, total_price)
1, 1, 100
2, 2, 100
3, 3, 150
4, 2, 300
account_payable:(ap_id, ord_id)
1, 1
2, 2
3, 3
Expected results: Vendor_name, total_price
Bond, 300 --this is the order not paid yet
select VENDOR.vendor_name, (ORDERS.ORD_TOTAL_COST + Orders.ord_total_cost)as "Total Amount Owed"
from ORDERS LEFT JOIN VENDOR ON
ORDERS.VENDOR_ID = VENDOR.VENDOR_ID
LEFT JOIN ACCOUNT_PAYABLE ON
ORDERS.ORD_ID = ACCOUNT_PAYABLE.ORD_ID
WHERE ORDERS.ORD_ID = ORDERS.ORD_ID
October 18, 2013 at 7:43 pm
I think this is close but still isn't working. It is saying no data found but i know there is.
select VENDOR.vendor_name, (ORDERS.ORD_TOTAL_COST + Orders.ord_total_cost)as "Total Amount Owed"
from VENDOR JOIN ORDERS ON
ORDERS.VENDOR_ID = VENDOR.VENDOR_ID
JOIN ACCOUNT_PAYABLE ON
ACCOUNT_PAYABLE.ORD_ID = ORDERS.ORD_ID
WHERE ACCOUNT_PAYABLE.ORD_ID NOT IN ( SELECT ORDERS.ORD_ID
FROM ORDERS
WHERE ORDERS.ORD_ID = ACCOUNT_PAYABLE.ORD_ID );
October 19, 2013 at 8:20 am
cdl_9009 (10/18/2013)
I think this is close but still isn't working. It is saying no data found but i know there is.select VENDOR.vendor_name, (ORDERS.ORD_TOTAL_COST + Orders.ord_total_cost)as "Total Amount Owed"
from VENDOR JOIN ORDERS ON
ORDERS.VENDOR_ID = VENDOR.VENDOR_ID
JOIN ACCOUNT_PAYABLE ON
ACCOUNT_PAYABLE.ORD_ID = ORDERS.ORD_ID
WHERE ACCOUNT_PAYABLE.ORD_ID NOT IN ( SELECT ORDERS.ORD_ID
FROM ORDERS
WHERE ORDERS.ORD_ID = ACCOUNT_PAYABLE.ORD_ID );
Didn't feel like creating the DDL for your tables or the scripts to populate them, so you will have to tell me if this works or not:
select
v.vendor_name,
o.total_price
from
Vendor v
inner join Order o
on (v.vendor_id = o.vendor_id)
where
not exists(select 1 from account_payable ap where ap.ord_id = o.ord_id);
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply