i have 5 tables in my database with same table structure
am trying to select from all the tables with like keyword then am getting an erro the error is
Column 'TitleLine' in where clause is ambiguous
the query am executing is
select * from equitymarketnews,economynews,corporatenews,industrynews where TitleLine Like '%axis Bank%' OR StoryMain like '%axis Bank%';
can anyone tell me where am doing wrong ?
i know this can be done BY UNION can anyone give me example ?
You probably don't want to design your tables like that but when you have the same named column and it could refer to either table you need to do tableName.columnName to make mysql understand. For a union, just break each of them into its own select then union them together like so (all one statement)
select * from equitymarketnews where TitleLine Like '%axis Bank%' OR StoryMain like '%axis Bank%'
UNION select * from economynews where TitleLine Like '%axis Bank%' OR StoryMain like '%axis Bank%'
UNION select * from corporatenews where TitleLine Like '%axis Bank%' OR StoryMain like '%axis Bank%'
UNION select * from industrynews where TitleLine Like '%axis Bank%' OR StoryMain like '%axis Bank%';
here is a good start on union
You need to qualify your columns in the where close. If TitleLine column is in the equitymarketnews table and the StoryMain is in the corporatenews, than your query should be
select * from equitymarketnews,economynews,corporatenews,industrynews where equitymarketnews.TitleLine Like '%axis Bank%' OR corporatenews.StoryMain like '%axis Bank%';
The other problem you have here is that your not joining between the tables. read up on inner joins http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
Use table alias if same column name is present.
Suppose you want to check from industrynews
then use industrynews.TitleLine Like '%axis Bank%'
If you want to check from all tables then use same for each table and as your expectation use AND, OR operator.
prefix your column name by table names
e.g. tablename.column
so your query becomes
select * from equitymarketnews,economynews,corporatenews,industrynews where TABLE_NAME.TitleLine Like '%axis Bank%' OR TABLE_NAME.StoryMain like '%axis Bank%';
replace TABLE_NAME with table name which holds that columns which optimizes your query
That just means that you have the same column name in 2 or more tables referenced in your query. You need to fully qualify the column name
