File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
JDBC and Relational Databases
mySQL Inner join
Joined: Jan 07, 2004
Oct 06, 2011 19:00:39
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 .
Joined: Dec 20, 2006
Oct 06, 2011 23:43:57
You can use 'not 'exists':
or 'not in':
OCUP UML fundamental and ITIL foundation
I agree. Here's the link:
subject: mySQL Inner join
Stumped on a SQL Statement
Another Enthuware question doubt regarding JPQL
Order in bind variable for PreparedStatement in Java
SQL subquery performs too slowly
Difference between inner and outer joins
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2015