In past conversations and models, we analyzed recovering from a solitary table, or recovering numerous qualities from different sources. Most genuine information activities are considerably more mind boggling, requiring accumulation, correlation, and recovery from numerous tables.
JOINs permit converging of at least two tables into a solitary article. They are utilized through SELECT, UPDATE, and DELETE proclamations.
Audit the overall punctuation of an assertion utilizing a JOIN as demonstrated beneath −
SELECT column
FROM table_name1
INNER JOIN table_name2
ON table_name1.column = table_name2.column;
Note the old punctuation for JOINS utilized certain joins and no watchwords. It is conceivable to utilize a WHERE statement to accomplish a join, yet catchphrases turn out best for lucidness, upkeep, and best practices.
JOINs come in numerous structures, for example, a left join, right join, or internal join. Different join types offer various kinds of total dependent on shared qualities or attributes.
Utilize a JOIN either at the order brief or with a PHP content.
The Command Prompt
At the order brief, just utilize a standard assertion −
root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> SELECT products.ID_number, products.Nomenclature, inventory.inventory_ct
FROM products
INNER JOIN inventory
ON products.ID_numbeer = inventory.ID_number;
+-------------+----------------+-----------------+
| ID_number | Nomenclature | Inventory Count |
+-------------+----------------+-----------------+
| 12345 | Orbitron 4000 | 150 |
+-------------+----------------+-----------------+
| 12346 | Orbitron 3000 | 200 |
+-------------+----------------+-----------------+
| 12347 | Orbitron 1000 | 0 |
+-------------+----------------+-----------------+
PHP Script Using JOIN
Utilize the mysql_query() capacity to play out a join activity −
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT a.product_id, a.product_manufacturer, b.product_count
FROM products_tbl a, pcount_tbl b
WHERE a.product_manufacturer = b.product_manufacturer';
mysql_select_db('PRODUCTS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Manufacturer:{$row['product_manufacturer']} <br> ".
"Count: {$row['product_count']} <br> ".
"Product ID: {$row['product_id']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
On effective information recovery, you will see the accompanying yield −
ID Number: 12345
Nomenclature: Orbitron 4000
Inventory Count: 150
--------------------------------------
ID Number: 12346
Nomenclature: Orbitron 3000
Inventory Count: 200
--------------------------------------
ID Number: 12347
Nomenclature: Orbitron 1000
Inventory Count: 0
--------------------------------------
mysql> Fetched data successfully