III. Retrieving Data from Multiple Tables - 05 Compound Join Conditions

Hi Mosh,

In the Compound Join Conditions , I did your help as the video to doesn’t show the output. Kindly revert soon.

With regards,
Ritu

SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id

Result:
order_id product_id quantity unit_price note_id order_Id product_id note

No record at all!

Mosh, do we need any changes the code?

Output:
3 31 15:37:12 SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id
LIMIT 0, 50000 0 row(s) returned 0.000 sec / 0.000 sec

SELECT *

FROM products

Output:

1 Foam Dinner Plate

2 Pork - Bacon,back Peameal - perishable

3 Lettuce - Romaine, Heart

4 Brocolinni - Gaylan, Chinese à types of Brocoli

two records

***Order_Items_Note : if the comments and remarks are specific is inclided it easier make update accordingly. ***

SELECT *

FROM order_items oi

JOIN order_item_notes oin

ON oi.order_id = oin.order_id

AND oi.product_id = oin.product_id

UPDATE order_item_notes

SET product_id=2 → it was product_id=4

WHERE order_id=1

UPDATE order_item_notes

SET order_id=5 → it was order_id=1

SELECT note_id, order_id, product_id, note

FROM order_item_notes

note_id order_id product_id note
1 1 2 first note
2 1 2 second note

SELECT order_id, product_id, quantity, unit_price

FROM order_items

order_id product_id quantity unit_price
1 4 4 3.74
2 1 2 9.1
2 4 4 1.66
2 6 2 2.94
3 3 10 9.12
4 3 7 6.99
4 10 7 6.4
5 2 3 9.89
6 1 4 8.65
6 2 4 3.28
6 3 4 7.46
6 5 1 3.45
7 3 7 9.17
8 5 2 6.94
8 8 2 8.59
9 6 5 7.28
10 1 10 6.01
10 9 9 4.28