Add JOIN

[mjovanc]

INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN to combine rows from two or more tables based on a related column between them.

[b4s36t4]

Want to work on this, would need some help though 😅

[mjovanc]

@b4s36t4 What you need to look at is the file njord/src/sqlite/query.rs which is the QueryBuilder for SELECT operations.

This one can be quite difficult/complex to implement if you are not sure from the get-go. Since with SQL you can do subqueries to pick data from multiple tables and combine them into a result table. At this moment, I'm not sure how that would be implemented. But feel free to explore the code. Maybe you don't need to come up with the whole solution, perhaps a proposed solution.

Otherwise, if this is too hard, I would suggest looking into other parts of Njord. Maybe you can start with a less big task, just fixing possible bugs, docs or refactor some parts you feel should be named differently etc.

[mjovanc]

An example of SQL that should be possible to generate is:

-- Query with JOIN inside a subquery
SELECT c.customer_id, c.customer_name, oe.order_id
FROM customers c
JOIN (
    SELECT o.order_id, o.customer_id
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    WHERE od.product_id = 123
) AS oe
ON c.customer_id = oe.customer_id;

Customers Table:

customer_id	customer_name
1	Alice
2	Bob
3	Carol

Orders Table:

order_id	customer_id
101	1
102	2
103	3

Order Details Table:

order_id	product_id
101	123
102	456
103	123

Result Table:

customer_id	customer_name	order_id
1	Alice	101
3	Carol	103