mySQL Inner join

[Jeppe Sommer]

Hello,

I am having a problem with a SQL query and inner join.

My problem is that I only want rows to be returned IF invoicelog.statusCode != 'OK' is true. The "statusCode = OK" may NOT exist in the rows being returned.

The query below returns one row with invoiceID = 2663, even that statusCode = OK does exists in one of the two rows being returned from invoicelog.

SELECT oioxmlinvoice.invoiceID FROM oioxmlinvoice INNER JOIN invoicelog ON oioxmlinvoice.invoiceID = invoicelog.invoiceID AND invoicelog.statusCode != 'OK' AND oioxmlinvoice.invoiceID = 2663 GROUP BY `oioxmlinvoice`.`invoiceID`

Running the query:

SELECT * FROM invoicelog WHERE invoiceID = 2663

it returns two rows:

InvoiceLogID       StatusCode
2663                   OK
2663                   ERROR

So how do I ensure that I don´t get any rows returned if "StatusCode = OK" exists in one of the rows from table invoicelog .

[Jan Cumps]

You can use 'not 'exists': http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html
or 'not in': http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html