If you want to display all the results in the same "table" [for example HTML table, but doesn't matter what type of visualisation is used], you need to define a fixed number of column for each table.
For example, if you have product [id, name, price, description, category] and blog [id, title, body], you can have a union of both using 3 columns [id, type and result].
- ID: Unique identifier for each type.
- TYPE: "blog", "product", etc, probably something that you can generate a link to the item.
- RESULT: Text where is show why this row was selected. You can concatenate multiple fields with
||.
Example:
$query = "[SELECT id, 'product', product_name || ' ' || product_description FROM tbl_products WHERE product_name LIKE '%{$searchTerms}%' OR product_description LIKE '%{$searchTerms}%']
UNION
[SELECT id, 'blog', blog_title || ' ' || blog_content FROM tbl_blog WHERE blog_title LIKE '%{$searchTerms}%' OR blog_content LIKE '%{$searchTerms}%']
UNION
[SELECT id, 'services', serviceCat_name FROM service_categories WHERE serviceCat_name LIKE '%{$searchTerms}%']";
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
| Companies | |
| +------------+-----------------+------+-----+---------+----------------+ | |
| | Field | Type | Null | Key | Default | Extra | | |
| +------------+-----------------+------+-----+---------+----------------+ | |
| | id | bigint unsigned | NO | PRI | NULL | auto_increment | | |
| | name | varchar[255] | NO | MUL | NULL | | | |
| +------------+-----------------+------+-----+---------+----------------+ | |
| +-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | |
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | | |
| +-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | |
| | companies | 0 | PRIMARY | 1 | id | A | 10106 | NULL | NULL | | BTREE | | | YES | NULL | | |
| | companies | 1 | companies_name_index | 1 | name | A | 8624 | NULL | NULL | | BTREE | | | YES | NULL | | |
| +-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | |
| Users | |
| +------------+-----------------+------+-----+---------+----------------+ | |
| | Field | Type | Null | Key | Default | Extra | | |
| +------------+-----------------+------+-----+---------+----------------+ | |
| | id | bigint unsigned | NO | PRI | NULL | auto_increment | | |
| | company_id | bigint unsigned | NO | MUL | NULL | | | |
| | first_name | varchar[255] | NO | MUL | NULL | | | |
| | last_name | varchar[255] | NO | MUL | NULL | | | |
| +------------+-----------------+------+-----+---------+----------------+ | |
| +-------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | |
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | | |
| +-------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | |
| | users | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | |
| | users | 1 | users_company_id_foreign | 1 | company_id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | |
| | users | 1 | users_first_name_index | 1 | first_name | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | |
| | users | 1 | users_last_name_index | 1 | last_name | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | |
| +-------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ |
How fetch data from multiple tables in php?
Each table has a column named DEPARTMENT_ID. This column is the primary key of the DEPARTMENTS table and a foreign key of the EMPLOYEES table. The below SELECT query joins the two tables by explicitly specifying the join condition with the ON keyword.
Can we fetch data from multiple tables using one query?
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.
How can I get data from multiple tables?
In SQL, to fetch data from multiple tables, the join operator is used. The join operator adds or removes rows in the virtual table that is used by SQL server to process data before the other steps of the query consume the data.
How do I search for a column in multiple tables in SQL?
Use this Query to search Tables & Views:.
SELECT COL_NAME AS 'Column_Name', TAB_NAME AS 'Table_Name'.
FROM INFORMATION_SCHEMA.COLUMNS..
WHERE COL_NAME LIKE '%MyName%'.
ORDER BY Table_Name, Column_Name;.