I want to get several tables from the database that are interconnected, but I can’t do it through INNER JOIN, how can I do it?

Question

MySQL

There are 4 tables: orders , users , booster_info , reviews
1) From the first table, orders , all columns are needed
2) From the second table, users , the username column is needed
3) From the third table, booster_info, the earned column is needed
4) From the fourth table, reviews , the id column is needed
1) Records should get as many as there are records in orders with the WHERE filter order_status = '5'
2) ​​Users has an id column that matches the booster_id columnin orders
3) Booster_info has a booster_id column that matches the booster_id column in orders
4) Reviews has an order_id column that matches the order_id column in orders At the same time, there may not be any entries in the reviews table (you just need to know if there is an entry order or not) The queries I was able to compose: SELECT o.*, b.earned AS booster_earned, u.username AS booster_name FROM orders AS o INNER JOIN booster_info AS b ON o.booster_id = b.booster_id INNER JOIN users AS u ON o.booster_id = u.id WHERE o.order_status='5'
The first query works as it should, but when I add the reviews table to it (it is empty, the second query), it stops producing results, the question is how to make the records still be retrieved as in the first query, but with an empty value in r.id AS review_id (empty of course if there are no corresponding records)
SELECT o.*, b.earned AS booster_earned, u.username AS booster_name, r.id AS review_id FROM orders AS o INNER JOIN booster_info AS b ON o.booster_id = b .booster_id INNER JOIN users AS u ON o.booster_id = u.id INNER JOIN reviews AS r ON o.order_id = r.order_id WHERE o.order_status='5'

Answer 1

in your case, you should use not the inner INNER JOIN, but the outer LEFT JOIN your_table_maybe_empty. it will turn out:

SELECT o.*, b.earned AS booster_earned, u.username AS booster_name, r.id AS review_id 
FROM orders AS o 
INNER JOIN booster_info AS b ON o.booster_id = b.booster_id 
INNER JOIN users AS u ON o.booster_id = u.id 
LEFT JOIN reviews AS r ON o.order_id = r.order_id 
WHERE o.order_status='5'

Answer 2

Read about JOIN types and at the same time about GROUP BY.

Answer 3

Here about non-strict connection