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.
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 ) ) )