File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL query to select all rows except one Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL query to select all rows except one" Watch "SQL query to select all rows except one" New topic
Author

SQL query to select all rows except one

andrew ronchi
Ranch Hand

Joined: Nov 03, 2011
Posts: 48

Hi,
I am using mysql as database and I am stuck at one point. I am not able to create proper sql query. Scenario is,
Suppose I have a table having firstname ,lastname and salary. I want to select all rows whose firstname starts from 'A' but not the record with lastname 'Ronchi'.
I have tried this query: SELECT * from tablename WHERE firstname REGEXP '^A' AND (firstname <> 'A' AND lastname <> "ronchi');
But it is giving unexpected result as it is negating the the '^A' result and giving me zero rows.

K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2628
    
    9

Why not use the like expression?

select * from tablename where firstname like 'A%' and lastname <> 'Ronchi'

K. Tsang JavaRanch SCJP5 SCJD/OCM-JD OCPJP7 OCPWCD5 OCPBCD5
andrew ronchi
Ranch Hand

Joined: Nov 03, 2011
Posts: 48

Actually we are creating dynamic queries so I can have multiple criteria on go and these can be REGEX sometimes. So in first criteria, it can ask me for all records with name starting from A but in second criteria it can ask me to exclude name Andy from first query result.

Below is my generated query for criteria : get count for firstname starting with "A" and firstname is "John" excluding one whose name starts with 'A' and lastname is Ronchi.

SELECT count(*) COUNT FROM table1 WHERE ( ( ( firstname REGEXP :param0 ) OR ( firstname = :prama1 ) ) AND ( ( firstname NOT REGEXP :param2 ) AND ( lastname <> :param3 ) ) )
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2628
    
    9

I see. Yet I don't see using regexp is good. It may make complicate your sql.

Also mixing conditions in a big sql can make your sql wrong. Using sub-query can make your logic easier to understand and debug.
andrew ronchi
Ranch Hand

Joined: Nov 03, 2011
Posts: 48

yeah You are right. But in this case can we have any solution?
andrew ronchi
Ranch Hand

Joined: Nov 03, 2011
Posts: 48

I have resolved this using dynamic nested queries.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL query to select all rows except one