How to search values from multiple tables using wildcards?

Question

I'm doing wild card searching with JDBC.

The code is working fine when I use a single table.

When I try to combine multiple tables using a wild card search, the code doesn't work.

For example, the user may search any keyword.

1. if the user searches for "cricket", the cricket keyword is availble in the sports table
2. if the user searches for "windows 8", it is available in the software table
3. if the user searches for "google, yahoo", the keywords are available in the website table

Here is the dynamic input value:

where s1(cricket,windows 8,google)

In the single table search I look for "cricket" in the sports table.

Here is my query, which works fine:

"select * from sports WHERE feed LIKE '%" +s1 + "%'";

My multiple table query does not work.

    "select * from  product  WHERE sitename LIKE '%"+s1+"%'" "OR 

    "select * from  sports     WHERE sitename LIKE '%"+s1+"%'" " OR

    "select * from  website  WHERE sitename LIKE '%"+s1+"%'" " OR

    "select * from  software  WHERE sitename LIKE '%"+s1+"%'" "OR

    "select * from   other   WHERE sitename LIKE '%"+s1+"%'"

What is wrong with this code?

Answer 1

Try to concat your single statements with a UNION instead of a OR. With this you can concat several SQL-Queries and tables with the same columns. For Example:

"select * from  product  WHERE sitename LIKE '%"+s1+"%' UNION 

 select * from  sports   WHERE sitename LIKE '%"+s1+"%' UNION

 select * from  website  WHERE sitename LIKE '%"+s1+"%' UNION

 select * from  software  WHERE sitename LIKE '%"+s1+"%' UNION

 select * from   other   WHERE sitename LIKE '%"+s1+"%'" ;

But be aware! Your single tables must have the same columns/ columncount, otherwise it won't work!
Maybe better example to clarify:

"select sitename, description from  product  WHERE sitename LIKE '%"+s1+"%' UNION 

 select sitename, description from  sports   WHERE sitename LIKE '%"+s1+"%' UNION

 select sitename, description from  website  WHERE sitename LIKE '%"+s1+"%' UNION

 select sitename, description from  software  WHERE sitename LIKE '%"+s1+"%' UNION

 select sitename, text as description from   other   WHERE sitename LIKE '%"+s1+"%'" ;

As you can see in Statement 6, you can refer other columns to your result by renaming them, but the columncount has to be the same.

Hope this may help you!