SQL Select * from Multiple Tables

SQL Select * from multiple tables

Yes, you can. The easiest way is with pdo, although there's at least a few other extensions which are capable of it.

pdo

Set the attribute on the PDO object, not the PDOStatment.

$PDO->setAttribute(PDO::ATTR_FETCH_TABLE_NAMES, true);

That's it. Then you get associative array keys like $row['myTable.myColumn']. It works if you fetch an object too (eg via PDO::FETCH_OBJECT) so beware, because you need to access the properties like $obj->{'myTable.myColumn'}

*The manual says the PDO::ATTR_FETCH_TABLE_NAMES attribute is only supported by certain drivers. If the above doesn't work, this might work instead.

$pdoStatement->setFetchMode(PDO::FETCH_NUM);
$pdoStatement->execute();
//build our associative array keys
$qualifiedColumnNames = array();
for ($i = 0; $i < $pdoStatement->columnCount(); $i++) {
    $columnMeta = $pdoStatement->getColumnMeta($i);
    $qualifiedColumnNames[] = "$columnMeta[table].$columnMeta[name]";
}

//fetch results and combine with keys
while ($row = $pdoStatement->fetch()) {
    $qualifiedRow = array_combine($qualifiedColumnNames, $row);
    print_r($qualifiedRow);
}

Same basic pattern is used for other database extensions

mysql

$res = mysql_query($sql);
//build our associative array keys
$qualifiedColumnNames = array();
for ($i = 0; $i < mysql_num_fields($res); $i++) {
    $columnMeta = mysql_fetch_field($res, $i);
    $qualifiedColumnNames[] = "$columnMeta[table].$columnMeta[name]";
}

//fetch results and combine with keys
while ($row = mysql_fetch_row($res)) {
    $qualifiedRow = array_combine($qualifiedColumnNames, $row);
    print_r($qualifiedRow);
}

mysqli

$res = $mysqli->query($sql);
//build our associative array keys
$qualifiedColumnNames = array();
foreach ($res->fetch_fields() as $columnMeta) {
    $qualifiedColumnNames[] = "{$columnMeta->table}.{$columnMeta->name}";
}

//fetch results and combine with keys
while ($row = $res->fetch_row()) {
    $qualifiedRow = array_combine($qualifiedColumnNames, $row);
    print_r($qualifiedRow);
}

This should also work with table aliases (tested in php 7.1) - the qualified column name will use the table alias.

SQL select statements with multiple tables

Select * from people p, address a where  p.id = a.person_id and a.zip='97229';

Or you must TRY using JOIN which is a more efficient and better way to do this as Gordon Linoff in the comments below also says that you need to learn this.

SELECT p.*, a.street, a.city FROM persons AS p
JOIN address AS a ON p.id = a.person_id
WHERE a.zip = '97299';

Here p.* means it will show all the columns of PERSONS table.

SQL select multiple tables

If there is no relationships between the 3 tables then you can just use UNION. It can be as simple as this:

SELECT receiver_id as event_id, event_name, event_description, event_timestamp FROM messages WHERE receiver_id = 10
UNION
SELECT client_id as event_id, event_name, event_description, event_timestamp FROM payments WHERE client_id = 10
UNION
SELECT receiver_id as event_id, event_name, event_description, event_timestamp FROM reports WHERE receiver_id = 10

This assumes there is a common set of columns across all tables that you are interested in. If however the columns have differing names but contain the same data (and datatypes), then you can use AS to rename the columns in the query to achieve the UNION

SELECT receiver_id as event_id, col1 as event_name, col2 as event_description, col3 event_timestamp FROM messages WHERE receiver_id = 10
UNION
SELECT client_id as event_id, col_a as event_name, col_b as event_description, col_c event_timestamp FROM payments WHERE client_id = 10
UNION
SELECT receiver_id as event_id, col_x as event_name, col_y as event_description, col_z event_timestamp FROM reports WHERE receiver_id = 10

Keep in mind that UNION will eliminate duplicate rows in your dataset. If this is not the behavior that you want then use UNION ALL instead (it will not eliminate duplicates).

Also keep in mind that if you column datatypes are not the same across the table then you will have to cast them to a common type. Casting is highly dependent of your DBMS so make sure you use the correct methods to do so.

SQL SELECT from multiple tables

SELECT p.pid, p.cid, p.pname, c1.name1, c2.name2
FROM product p
LEFT JOIN customer1 c1 ON p.cid = c1.cid
LEFT JOIN customer2 c2 ON p.cid = c2.cid

SQL query to select data from two tables

You have to use left join :

SELECT Invitations.*, Users.UserName
FROM     Invitations left JOIN
              Users ON Invitations.UserID = Users.UserID

MySQL - Select from multiple tables and display multiple fields

The error says that you have tried to query two tables that each have an option_name column and the DB has no way of knowing which of the two you mean.

What you are doing by specifying two tables in the FROM clause is an implicit join. If you want columns from two tables joined in your result, you would need to specify which columns you mean by prefixing them with the table name, e.g.

SELECT wpm_104_options.option_name, wpm_104_options.option_value, wpm_101_options.option_name, wpm_101_options.option_value 
FROM wpm_104_options,wpm_101_options
WHERE wpm_104_options.option_name = 'admin_email' 
GROUP BY wpm_104_options.option_name

However, this usually only makes sense if you also specify in which way the columns are supposed to be joined, i.e. define their relationship in the WHERE clause. Otherwise you'd just get every possible combination of rows from the two tables.

I guess what you really want is something else though. I believe you simply want the combined results of the query you showed for one table, but from two tables. This you don't achieve with an implicit join with two tables in the FROM clause but instead by using UNION or UNION ALL like you already did. E.g.

SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'admin_email' 
UNION
SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'siteurl'
UNION
SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'blogname'
UNION 
SELECT option_name, option_value 
FROM wpm_101_options
WHERE option_name = 'admin_email' 
UNION
SELECT option_name, option_value 
FROM wpm_101_options
WHERE option_name = 'siteurl'
UNION
SELECT option_name, option_value 
FROM wpm_101_options
WHERE option_name = 'blogname'

You can also greatly shorten this by just specifying the different conditions in the WHERE clause with OR:

SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'admin_email' 
   OR option_name = 'siteurl'
   OR option_name = 'blogname'
UNION 
SELECT option_name, option_value 
FROM wpm_101_options
WHERE option_name = 'admin_email' 
   OR option_name = 'siteurl'
   OR option_name = 'blogname'

or even

SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name IN ('admin_email', 'siteurl', 'blogname')
UNION 
SELECT option_name, option_value 
FROM wpm_101_options
WHERE option_name IN ('admin_email', 'siteurl', 'blogname')

Can SELECT * FROM multiple tables with same _TABLE_SUFFIX pattern

As per this documentation, when using wildcard tables, all the tables in the dataset that begin with the table name before * are scanned even if _TABLE_SUFFIX is used in combination with REGEXP_CONTAINS. In our case, the wildcard pattern is client_id_* and hence, the values such as client_id_1_campaigns are also matched irrespective of the pattern in REGEXP_CONTAINS.

The reason for this behaviour is that, the wildcard pattern precedes the regex and scans all the tables matching the wildcard pattern and will not take the regex into account. Using wildcards while also using REGEXP_CONTAINS is applying regex on top of regex and is not recommended.

If you wish to have the intended target tables you will need to use the below query instead of using wildcards to query multiple tables.

SELECT *
FROM (
SELECT * FROM `project-id.dataset-id.client_id_2_campaign_performance_overview` UNION ALL
SELECT * FROM `project-id.dataset-id.client_id_7_campaign_performance_overview` UNION ALL
SELECT * FROM `project-id.dataset-id.client_id_10_campaign_performance_overview`);

Using the LIKE operator also does not give the expected results for the same reason mentioned above. The tables are scanned first then filtered giving extra columns in the result.

Also, BigQuery uses the schema for the most recently created table that matches the wildcard as the schema for the wildcard table. Even if you restrict the number of tables that you want to use from the wildcard table using the _TABLE_SUFFIX pseudo column in a WHERE clause, BigQuery uses the schema for the most recently created table that matches the wildcard. You will see the extra columns in the result if the most recently created table has them.

SQL select data from multiple tables sorted by a column within each category

Unless I'm missing something here, a simple order by should do it:

select table1.sym,
    table1.i_date,
    table1.value
from table1
inner join table2 on table1.sym = table2.sym
where table2.mapping = 'MP1'
order by table1.sym, table1.i_date

SQL Select and Sort from Multiple tables

If by display 5 latest/newest products, included from each table you mean 5 latest from the combined result set, a view using a UNION will do the job:

create view testvw as select * from 
(
(select productname,manufacturer,arrivaldate from Clothes)
UNION
(select productname,manufacturer,arrivaldate from Toys)
UNION
(select productname,manufacturer,arrivaldate from Tools)
) x 
order by arrivaldate desc limit 5;