CODEWITHSUNDEEP
phpmysql
Manoj Kumar
Manoj Kumar

Reputation: 646

Select From Multiple Tables mysql

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 ?

Upvotes: 1

Views: 1715

Answers (5)

hackartist
hackartist

Reputation: 5264

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

Upvotes: 1

Amine Zegl
Amine Zegl

Reputation: 71

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

Upvotes: 1

Icarus
Icarus

Reputation: 63956

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

Upvotes: 0

Jaspreet Chahal
Jaspreet Chahal

Reputation: 2750

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

Upvotes: 0

Somnath Muluk
Somnath Muluk

Reputation: 57656

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.

Upvotes: 0

Related Questions