How to show data with combining of 2 tables?-CodePudding

I have a table named purchase_details_master and another is sales_detais_master Where all the purchase and sales details are available.

Structure of Purchase Table: These are the data which I have purchased

Structure of Sales Table: These are the data which I have sell

Now I need to show the data like this, This image is showing the iPhone 12 data. In the Inward field showing tha pieces of purchased item, In the Outward field showing the data of sells pieces, and the closing data showing like Opening Inward or Opening - Outward = Closing

I am using Core Php with Sqlite database

$item = $_POST['item'];
$sql = "SELECT * FROM purchase_details_master WHERE item_id = '$item'";
$sql_run = $conn->query($sql);

I have used while loop it's not solving the problem Kindly solve this out for me! Thank you in Advance

CodePudding user response：

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

like that.

SELECT * FROM purchase_details_master AS pdm INNER JOIN sales_detais_master AS sdm ON sdm.item_id = pdm.item_id WHERE sdm.item_id = ?

CodePudding user response：

this will select all fields from the two tables

    $sql = "SELECT * FROM purchase_details_master, sales_detais_master  WHERE purchase_details_master.item_id = '$item' AND purchase_details_master.item_id = sales_details_master.item_id";

or select exact fields from the two tables

SELECT table1.field1, table1.field2, table2.field1, table2.field3 
FROM table1, table2, 
WHERE  table1.field1 = something, or table1.field1 = table2.field2