Jassif Team Institution

let mysql = require('mysql');

let con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  let sql = "SELECT users.name AS user, products.name AS favorite FROM users JOIN products ON users.favorite_product = products.id";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

C:\Users\Your Name>node demo_db_join.js

[
  { user: 'John', favorite: 'Chocolate Heaven' },
  { user: 'Peter', favorite: 'Chocolate Heaven' },
  { user: 'Amy', favorite: 'Tasty Lemons' }
]

SELECT users.name AS user,
products.name AS favorite
FROM users
LEFT JOIN products ON users.favorite_product = products.id

[
  { user: 'John', favorite: 'Chocolate Heaven' },
  { user: 'Peter', favorite: 'Chocolate Heaven' },
  { user: 'Amy', favorite: 'Tasty Lemons' },
  { user: 'Hannah', favorite: null },
  { user: 'Michael', favorite: null }
]

SELECT users.name AS user,
products.name AS favorite
FROM users
RIGHT JOIN products ON users.favorite_product = products.id

[
  { user: 'John', favorite: 'Chocolate Heaven' },
  { user: 'Peter', favorite: 'Chocolate Heaven' },
  { user: 'Amy', favorite: 'Tasty Lemons' },
  { user: null, favorite: 'Vanilla Dreams' }
]

// Join three tables: users, orders, products
let sql = `
  SELECT 
    users.name AS customer,
    products.name AS product,
    orders.quantity,
    orders.order_date
  FROM users
  INNER JOIN orders ON users.id = orders.user_id
  INNER JOIN products ON orders.product_id = products.id
  ORDER BY orders.order_date DESC
`;

con.query(sql, function (err, result) {
  if (err) throw err;
  console.log("Three table join result:", result);
});

// JOIN with GROUP BY and aggregation
let sql = `
  SELECT 
    users.name AS customer,
    COUNT(orders.id) AS total_orders,
    SUM(orders.amount) AS total_spent
  FROM users
  LEFT JOIN orders ON users.id = orders.user_id
  GROUP BY users.id, users.name
  ORDER BY total_spent DESC
`;

con.query(sql, function (err, result) {
  if (err) throw err;
  console.log("Customer order summary:", result);
});

// JOIN with subquery
let sql = `
  SELECT 
    users.name,
    recent_orders.order_count
  FROM users
  LEFT JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders 
    WHERE order_date >= '2023-01-01'
    GROUP BY user_id
  ) recent_orders ON users.id = recent_orders.user_id
`;

con.query(sql, function (err, result) {
  if (err) throw err;
  console.log("Users with recent order counts:", result);
});

const mysql = require('mysql');

// Create connection
const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  database: "company_db"
});

// Connect to MySQL
con.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  
  console.log('Connected to MySQL database');
  
  // Example 1: INNER JOIN
  console.log('\n1. INNER JOIN - Users with favorite products:');
  con.query(`
    SELECT users.name AS user, products.name AS favorite 
    FROM users 
    INNER JOIN products ON users.favorite_product = products.id
  `, (err, result) => {
    if (err) {
      console.error('Error in INNER JOIN:', err.message);
      return;
    }
    console.log(result);
  });
  
  // Example 2: LEFT JOIN
  console.log('\n2. LEFT JOIN - All users with their favorite products:');
  con.query(`
    SELECT users.name AS user, products.name AS favorite 
    FROM users 
    LEFT JOIN products ON users.favorite_product = products.id
  `, (err, result) => {
    if (err) {
      console.error('Error in LEFT JOIN:', err.message);
      return;
    }
    console.log(result);
  });
  
  // Example 3: RIGHT JOIN
  console.log('\n3. RIGHT JOIN - All products with users who favorited them:');
  con.query(`
    SELECT users.name AS user, products.name AS favorite 
    FROM users 
    RIGHT JOIN products ON users.favorite_product = products.id
  `, (err, result) => {
    if (err) {
      console.error('Error in RIGHT JOIN:', err.message);
      return;
    }
    console.log(result);
  });
  
  // Example 4: Multiple table JOIN
  console.log('\n4. Three table JOIN - Users, orders, and products:');
  con.query(`
    SELECT 
      u.name AS customer,
      p.name AS product,
      o.quantity,
      o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN products p ON o.product_id = p.id
    ORDER BY o.order_date DESC
    LIMIT 5
  `, (err, result) => {
    if (err) {
      console.error('Error in three table JOIN:', err.message);
      return;
    }
    console.log(result);
    
    // Close connection
    setTimeout(() => {
      con.end((err) => {
        if (err) {
          console.error('Error closing connection:', err.message);
          return;
        }
        console.log('\nConnection closed');
      });
    }, 1000);
  });
});