我需要有關簡單 SQL 查詢的幫助。假設我有兩張表，客戶和訂單。在customers 表中我只有name 和id 欄位，在orders 表中我有name、id 和customer_id 欄位。這里 customer_id 是客戶表的外鍵。
所以現在我想獲取所有客戶并加入他們的每個訂單。假設這些是帶有虛擬資料的兩個表：
customers table
id name
1 John Doe
2 Jane Doe
orders table
id product_name customer_id
250 Massage Gun 1
260 Mac Lipstick 2
270 Mac Eyeliner 2
280 Yoga Mat 1
290 Mac Eyeshadow 2
這是代碼：
const query = `
SELECT * FROM customers c WHERE EXISTS (SELECT * FROM orders o WHERE o.customer_id = c.id);
`;
const customers = await pool.query( query );
console.log( customers );
uj5u.com熱心網友回復：
您的 SQL 陳述句執行半連接，它只檢查訂單是否存在，但不將它們包含在結果集中。你想加入：
SELECT * FROM customers c JOIN orders o
ON o.customer_id = c.id
uj5u.com熱心網友回復：
// construct your query 1st
const query = `SELECT c.*, o.id as orderId, o.product_name
FROM customers c
INNER JOIN orders o
ON(c.id = o.customer_id);`;
// try-catch pool.query since it can raise exception
try{
const customers = await pool.query(query);
let customers_dict = {};
for(var i = 0; i<customers.length; i ){
if(!(customers[i].id.toString() in customers_dict)){ // 1st order
customers_dict[customers[i].id.toString()] = {
"id": customers[i].id,
"name": customers[i].name,
"orders":[
{
"id": customers[i].orderId,
"product_name":customers[i].product_name,
}
]
};
}else{ // another order for the customer id
customers_dict[customers[i].id.toString()]["orders"].push({
"id": customers[i].orderId,
"product_name": customers[i].product_name,
});
}
}
//here is the expected final json result
const expected_json_customers = Object.values(customers_dict);
console.log(expected_json_customers);
}catch(err){
console.log(err); // your error mgmt function
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/411367.html
標籤：
下一篇：使用fetch時服務器端的空正文