CODEWITHSUNDEEP
phpmysqlsqldatabasejoin
EngineerAkki
EngineerAkki

Reputation: 114

SQL query for Selecting from Multiple Tables in single database

I am having 3 tables (c19 , c19b2, g26) in a database

I want to write a SQL Query to search and display all fields of the matched record.

I am using following query:

$query = "SELECT * FROM c19,c19b2,g26 WHERE armyno LIKE '%$searchTerm%'";

But it only works for table c19, Data from the other 2 tables is not fetched.Each table has a field armyno

Please help me with this Thank you.

Upvotes: 0

Views: 79

Answers (2)

Kirk Backus
Kirk Backus

Reputation: 4866

Alright, you are not looking for a JOIN, but a UNION.

SELECT * FROM c19 WHERE armyno LIKE '%$searchTerm%'
UNION
SELECT * FROM c19b2 WHERE armyno LIKE '%$searchTerm%'
UNION
SELECT * FROM g26 WHERE armyno LIKE '%$searchTerm%'

That will let you query all three tables at the same time.

Upvotes: 1

Greg
Greg

Reputation: 2600

Which DB are you using? This would have worked in SQL Server. However, notice you are doing a cross join of every record to every record... usually you only want to match some records by restriction of a matching key, for example:

select 
  *
from a 
left join b on b.somekey = a.somekey
left join c on c.someotherkey = b.someotherkey

In SQL server you can just say *, but I'm taking it that in your DB engine that didn't work, so try specifying which table. This may in some environments require aliasing as well:

select 
  a.*,
  b.*,
  c.*
from tableA as a 
left join tableB as b on b.somekey = a.somekey
left join tableC as c on c.someotherkey = b.someotherkey

Generally, you should see the columns from the first table, followed by the columns from the second table, followed by columns from the third table for a given row. If you wanted to get all columns from all tables, but separately, then that would be 3 separate selects.

Lastly, if all 3 tables have "armyno" then I'd expect it to throw an ambiguous field error. In such case you'd want to specify which table's "armyno" field to filter on.

Upvotes: 0

Related Questions