https://www.w3schools.com/sql/sql_join.asp
We have a database table 'orders' where orders are stored with their prices.
When we want to retrieve a particular order we can do that with a query as follows:
$query1 = "
SELECT
`id`, `order_id`, `customer_email`, `invoice_date`
FROM
`orders`
WHERE
`customer_email` LIKE 'nr1@ericbontenbal%'
AND
`invoice_date` LIKE '2018-11-16 15:30:56'
AND
";
Result
| id | order_id | customer_email | invoice_date |
|---|---|---|---|
| 1363078 | 33881728 | nr1@ericbontenbal.nl | 2018-11-16 15:30:56 |
in the same database we also have a table 'order_product' which shows which products are sold with an order.
To retrieve the products that go with that particular order we can do the following query:
$query2 = "
SELECT
`name`, `quantity`, `price`, `total_inc_btw`, `total_inc_btw_euro`
FROM
`order_product`
WHERE
`orders_id` = 1363078;
";
Result
| name | quantity | price | total_inc_btw | total_inc_btw_euro | |
|---|---|---|---|---|---|
| Sticky Strip | 1 | 3.000000 | 3.000000 | 2.648460 | |
| Northern Light Feminized | 1 | 30.000000 | 30.000000 | 25.649790 | |
| Master Kush Feminized | 1 | 0.000000 | 30.000000 | 25.649790 | |
| Sour Diesel Feminized | 1 | 42.000000 | 42.000000 | 35.909706 |
Instead of doing 2 queries we can combine them to one query when we use a 'join'.
$query3 = "
SELECT
o.`order_id`,
o.`customer_email`,
o.`invoice_date`,
o.`currency`,
op.`name`,
op.`quantity`,
op.`price`,
op.`total_inc_btw`,
op.`total_inc_btw_euro`
FROM
`orders` o
JOIN
`order_product` op
ON
o.`id` = op.`orders_id`
WHERE
o.`customer_email` LIKE 'nr1@ericbontenbal%'
AND
o.`invoice_date` LIKE '2018-11-16 15:30:56'
AND
o.`currency` = 'USD';
";
Result
| order_id | customer_email | invoice_date | currency | name | quantity | price | total_inc_btw | total_inc_btw_euro | |
|---|---|---|---|---|---|---|---|---|---|
| 33881728 | nr1@ericbontenbal.nl | 2018-11-16 15:30:56 | USD | Sticky Strip | 1 | 3.000000 | 3.000000 | 2.648460 | |
| 33881728 | nr1@ericbontenbal.nl | 2018-11-16 15:30:56 | USD | Northern Light Feminized | 1 | 30.000000 | 30.000000 | 25.649790 | |
| 33881728 | nr1@ericbontenbal.nl | 2018-11-16 15:30:56 | USD | Master Kush Feminized | 1 | 30.000000 | 30.000000 | 25.649790 | |
| 33881728 | nr1@ericbontenbal.nl | 2018-11-16 15:30:56 | USD | Sour Diesel Feminized | 1 | 42.000000 | 42.000000 | 35.909706 |