Node.js MySQL Join

Next Page >>
Nodejs tutorial

Node.js Tutorial » Node.js MySQL Join

How to Join Two or More Tables?

Using a "JOIN" statement we can combine rows from two or more tables, based on a related column between them.

Let us to have a table "Users" as:

ID	name	favorite_prod	value
1	Clark K	245	31
2	John Carter	246	12
3	Peter Parker	245	7
4	Johny Alson	245	2.5
5	Pierre Alexander	248	9.3
6	Nick Boss		24
7	Json Nei		2.9

Another table named "Products"

ID	name_prod
244	Salad greens
245	Chocolate
246	Lemons
247	Ice Cream
248	Apple
249	Pears
250	Onion greens

Combine two tables using JOIN statement

With JOIN statement we ombine two tables "Users" favorite_product column and "Products" id column.

Example: UPDATE records

var mysql = require('mysql');

var conn = mysql.createConnection({
host: "localhost",
user: "your_username",
password: "your_password",
database: "my_db_name"
});

conn.connect(function(err) {
if (err) throw err;
var sql = "SELECT Users.name AS user, Products.name AS favorite FROM Users JOIN Products ON Users.favorite_prod = prod.id";
conn.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});
});

Note: You can replace JOIN with INNER JOIN. They will display the same result.

Save the code above in a file named "demo_test_db_join.js", and run the file:

E:\Users\Your_Name>node demo_test_db_join.js

Which will give you this result after run the file your table will be:

user	favorite
Clark K	Chocolate
John Carter	Lemons
Peter Parker	Chocolate
Johny Alson	Chocolate
Pierre Alexander	Apple

Note: only the records with a match in both tables are returned.

Tags: node js mysql join query
query in node js
nodejs path join
sequelize join multiple tables
how to join two collections in mongodb using node js

MySQL Left Join

If we need to display all users, it doesn't matter if they have it or not favorite product, use LEFT JOIN statement:

Select all users and their favorite product:

Example:

SELECT users.name AS user,
products.name AS favorite
FROM Users
LEFT JOIN Products ON Users.favorite_prod = products.id

Save the code above in a file called "demo_test_db_join2.js", and run the file:

Example: : Run "demo_test_db_join2.js"

E:\Users\Your_Name>node demo_test_db_join2.js

MySQL Right Join

Select all products and the user who have them as their favorite:

Example:

SELECT users.name AS user,
products.name AS favorite
FROM users
RIGHT JOIN Products ON Users.favorite_prod = products.id

Note: the rows, without favorite product, are not included in the result

node js mysql join query
query in node js
nodejs path join
sequelize join multiple tables
how to join two collections in mongodb using node js
Node.js MySQL Join - nodejs

Online Editor

ONLINE EDITOR

news templates

COLOR PICKER

news templates

This tool makes it easy to create, adjust, and experiment with custom colors for the web.

HTML Templates

Magnews2 is a modern and creative free magazine and news website template that will help you kick off your online project in style.

CSS HTML Layout

Find here examples of creative and unique website layouts.

Free CSS HTML Menu

Find here examples of creative and unique website CSS HTML menu.