Unable to join and fetch data from two tables that are referenced with foreign key-CodePudding

I need help with a simple SQL query. Suppose I have two tables, customers and orders. In customers table I have only name and id fields and in orders table I have name, id and customer_id fields. Here customer_id is the foreign key to customers table.

So now I want to fetch all customers and also join each of their orders. Suppose these are the two tables with dummy data:

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

Here's the code:

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 );

CodePudding user response：

Your SQL statement performs a semi-join, which only checks the existence of orders, but does not include them in the result set. You want a join:

SELECT * FROM customers c JOIN orders o
           ON o.customer_id = c.id

CodePudding user response：

// 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
}