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.
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.
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.
