CODEWITHSUNDEEP
javamysqlsqldatabasesearch
David Gidony
David Gidony

Reputation: 1283

Search within the search results of an MYSQL Query

I'm writing an app in java, that receives requests and save them to a file, then it can search the old requests.

I started to upgrade my app to use MySQL instead of a plain Txt file.

I am looking for a way to query the DataBase in the following way:

I am looking for requests that were opened in (Specific Dates)-First Search , that contain the word xxx -second search

I found this query:

SELECT * FROM requestserverdb.closedrequests WHERE ClosingTimeStamp LIKE "%2014-09-01%" 
OR ClosingTimeStamp LIKE "%2014-09-02%" ;

This gives me all the lines that was created in these dates, I want the query to return only the lines within these results that contains the specific word too...

For example :

this query will return:

1,bannaba, i want to be alive,2014-09-01

6,apple, woo hoo, 2014-09-02

7,Mango, groove is in the heart,2014-09-02

i need a query that will return only lines that contain the word "apple" in one of its columns:

6,apple, woo hoo, 2014-09-02

Upvotes: 0

Views: 108

Answers (3)

mfredy
mfredy

Reputation: 641

Rather than searching for the date, why don't you just search for a record which contains the word apple? The other way could be is to have 2 WHERE conditions. One that contins the date and one that contains the word Apple

Upvotes: 0

Jose Martinez
Jose Martinez

Reputation: 11992

Use nested SELECT. But the inner SELECT should not use * so that the collumn can be referenced from the outer SELECT.

SELECT col_1 (SELECT col_1, col_2 FROM requestserverdb.closedrequests WHERE ClosingTimeStamp LIKE "%2014-09-01%" OR ClosingTimeStamp LIKE "%2014-09-02%") WHERE col_1 LIKE "%XXX%";

something to that affect.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Your query is a very bad way to search for date times. The following is a bit better:

SELECT *
FROM requestserverdb.closedrequests
WHERE date(ClosingTimeStamp) = '2014-09-01' OR date(ClosingTimeStamp) = 2014-09-02';

If you have no time component to the time stamp, then remove the date() function. Only use like on strings.

An even better way is:

SELECT *
FROM requestserverdb.closedrequests
WHERE ClosingTimeStamp >= '2014-09-01' AND ClosingTimeStamp < 2014-09-03';

The latter will allow the engine to use an index on ClosingTimeStamp.

If you want to add another condition, just add it:

SELECT *
FROM requestserverdb.closedrequests
WHERE ClosingTimeStamp >= '2014-09-01' AND ClosingTimeStamp <= 2014-09-03' AND
      col2 = 'Apple';

SQL doesn't have a built-in concept of saving query results for another query. You can implement such functionality, but it is probably not needed for your application. You can just add conditions to the where clause.

Upvotes: 1

Related Questions