Menu
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.
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?
383
The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator. The percent sign represents zero, one or multiple characters. The underscore represents a single number or character.
Even if you restrict the number of tables that you want to use from the wildcard table using the _TABLE_SUFFIX pseudo column in a WHERE clause, BigQuery uses the schema for the most recently created table that matches the wildcard.
To retrieve information from more than one table, you need to join those tables together. This can be done using JOIN methods, or you can use a second SELECT statement inside your main SELECT query—a subquery.
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!
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
