• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL query to select all rows except one

 
andrew ronchi
Ranch Hand
Posts: 50
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3585
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why not use the like expression?

select * from tablename where firstname like 'A%' and lastname <> 'Ronchi'
 
andrew ronchi
Ranch Hand
Posts: 50
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3585
16
Android Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 50
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yeah You are right. But in this case can we have any solution?
 
andrew ronchi
Ranch Hand
Posts: 50
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have resolved this using dynamic nested queries.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic