Home » Node.js

Select data using identical field from MYSQL table by using Node.js

In this article, we are going to learn how to select data from two tables using identical field in MYSQL table using Node.js server?
Submitted by Manu Jemini, on December 29, 2017

Prerequisite/recommended:

• Create MYSQL database by using Node.js
• Create table in MYSQL database by using Node.js
• Create another table in same database by using Node.js
• Insert data in MYSQL table by using Node.js
• Show data from MYSQL table by using Node.js
• Delete data from MYSQL table by using Node.js
• Update data of MYSQL table by using Node.js
• Count number of records in MYSQL table by using Node.js
• Search record with two fields from MYSQL table by using Node.js
• Search record with a pattern from MYSQL table by using Node.js

We need two fields which are identical for both our tables to select data with below query.

The very first thing is to prepare a server file of node.js where we require the MySQL module to work with MySQL and after that, we prepare a connection by using mysql.createConnection() method of MySQL module.

Then attributes like a host, user, password and database name are used define inside our create connection method.

Now the last thing, all we need to create an SQL query basically a select query with an identical field to select data from MySQL table and after that we can use query() method to execute query statement with a callback in which we can throw the error if any and print the affected values.

Database details:

• Hostname: localhost
• Port number: 3306
• Username: root
• Password: 123
• Database: demo
• Tables name: clients, city

Note: Identical fields and their values have to be same for retrieving data.

Steps, we are need to follow:

• Require MySQL module.

• Create Connection variable using mysql.createConnection() method.
• Connect by using con.connect() method.
• Creating a select SQL query to select data from both the tables.
• Execute query by using .query() method.
• Show result.

Server file:

//step-1
var mysql = require('mysql');
//step-2
var con = mysql.createConnection({
  host: "127.0.0.1",
  user: "root",
  password: "123",
  database: "demo"
});
//step-3
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "Select A.*, B.* from clients A,city B where A.id=B.id";

//step-4
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output