May 16, 2021 MariaDB
In previous discussions and examples, we examined retrieving multiple values from a single table or from multiple sources. M ost real-world data operations are much more complex and require aggregation, comparison, and retrieval from multiple tables.
JOIN allows two or more tables to be merged into a single object. T hey are used through SELECT, UPDATE, and DELETE statements.
The general syntax for viewing statements using JOY is as follows -
SELECT column FROM table_name1 INNER JOIN table_name2 ON table_name1.column = table_name2.column;
Note THATS's old syntax used implicit connections and no keywords. W here clauses can be used for joins, but keywords are best suited for readability, maintenance, and best practices.
JOIN has many forms, such as left, right, or inner. V arious connection types provide different types of aggregation based on shared values or attributes.
Use JOIN in command prompts or PHP scripts.
At the command prompt, simply use the standard statement -
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 | +-------------+----------------+-----------------+
Use the mysql_query() function to perform a connection operation -
<?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 "; mysql_close($conn); ?>
After a successful data retrieval, you will see the following output -
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