Urgent: Return results if not in specific table + column

[aebstract]

Title sucks, not sure what to name it. Anyway, I've got a database with lots of tables and lots of information. (our inventory management software) I'm doing something where I am returning results of orders, and I need to only return it if "SO.ID" does not show up in "POSTRANSACTION.SOID". Basically in the query I have "AND POSTRANSACTION.SOID = SO.ID" which did the exact opposite of what I needed (obviously). So it is only showing the orders that are in the postransaction table right now, and it needs to only show ones that AREN'T. Is there a simple query word or two I can use to do this?

[kickstart]

Hi

 

I think what you want is a left outer join and then look for null records.

 

Something like

 

SELECT *
FROM SO 
LEFT OUTER JOIN POSTRANSACTION
ON SO.ID = POSTRANSACTION.SOID
WHERE POSTRANSACTION.SOID IS NULL

 

All the best

 

Keith

[aebstract]

Okay I tried adding the bit you put in to the code, and got an error. Here is the code to look at + the error:

 

SELECT SO.num AS "SO", SO.dateCompleted AS "DateCompleted", SO.dateIssued as "DateIssued",
    COALESCE(SO.totalTax, 0) as "Tax", COALESCE(POSTransaction.Amount, 0) AS "POSAmount",
    POSTransaction.dateTime as "POSDate", PaymentMethod.name AS "PaymentName", Customer.name as "Customer", so.customerpo as "customerpo",
    COALESCE(SUM(SOItem.totalPrice), 0) AS totalPrice, MAX(SOItem.dateScheduledFulfillment) AS DateScheduledFulfillment,
    PaymentTerms.netDays as "NetDays", PaymentTerms.nextMonth as "NextMonth",
    PaymentMethod.Name as "PaymentType", company.name AS company, COALESCE(postcatch.totalpaid, 0) AS totalpaid,
    COALESCE(postcatchcount.countpaid,1) as catchcount

FROM Customer, SOItem, PaymentTerms, SO
    LEFT JOIN POSTransaction ON POSTransaction.soID = SO.ID
    LEFT JOIN PaymentMethod ON PaymentMethod.ID = POSTransaction.paymentMethodID
    JOIN company ON company.id = 1
    LEFT JOIN (SELECT SUM(amount) AS totalpaid, soid
                 FROM postransaction
                GROUP BY soid) postcatch ON postcatch.soid = so.id
    LEFT JOIN (SELECT Count(amount) AS countpaid, soid
                 FROM postransaction
                GROUP BY soid) postcatchCount ON postcatchCount.soid = so.id
    LEFT OUTER JOIN postransaction ON SO.ID = POSTRANSACTION.SOID


WHERE Customer.ID = SO.customerID
AND SOItem.SOID = SO.ID
AND PaymentTerms.ID = SO.paymentTermsID
AND SOItem.typeID <> 40
AND Customer.ID LIKE $P{customerID}
AND SOItem.statusID <> 70
AND SO.statusID IN($P{ckIssued},$P{ckInProgress},$P{ckFulfilled},$P{ckClosedShort})
AND POSTRANSACTION.SOID IS NULL

GROUP BY SO.num, SO.dateCompleted, SO.dateIssued, SO.statusID, SO.totalTax,
    POSTransaction.amount, POSTransaction.dateTime, PaymentMethod.name, Customer.name,
    PaymentTerms.netDays, PaymentTerms.typeID, PaymentTerms.nextMonth, company.name,
    postcatch.totalpaid, postcatchcount.countpaid, so.customerpo

ORDER BY Customer.name, SO.num, POSTransaction.dateTime

 

 

Error:SQL problems:GDS Exception. 335544569. Dynamic SQL ErrorSQL error code = -204Ambiguous field name between table POSTRANSACTION and table POSTRANSACTION SOID

[kickstart]

Hi

 

You have the table POSTRANSACTION in there twice. You need to give an alias to (at least) one of them. Although looking at you code I think you can just remove one of the JOINs to that table.

 

All the best

 

Keith

[aebstract]

Deleted the new one that I added since I didn't notice the first. Seems to have worked. Thanks