MySQL JOIN MULTIPLE TABLES

 How to Join Multiple Tables:

How to work Multiple Tables In MySql:

Multiple Join Syntax:

Code:

CREATE TABLE personal(

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

    percentage INT NOT NULL,

age INT NOT NULL,

gender VARCHAR(1) NOT NULL,

city INT NOT NULL,

    courses INT NOT NULL,

    PRIMARY KEY (id),

FOREIGN KEY (city) REFERENCES City (cid),

    FOREIGN KEY (courses) REFERENCES Courses (course_id)  

);

INSERT INTO personal(id,name,percentage,age,gender,city,courses)

VALUES

(1,"Aqib","45","13","M",1,1),

(2,"Khadija","56","21","F",2,2),

(3,"Salman Khan","62","20","M",1,1),

(4,"Aqeela","47","18","F",3,1),

(5,"Saqib","74","22","M",1,3),

(6,"Abraham","64","21","M",2,2),

(7,"Shahid","52","20","M",1,3);

create table "city" and insert records

CREATE TABLE city(
    cid INT NOT NULL AUTO_INCREMENT,
    cityname VARCHAR(50) NOT NULL,
    PRIMARY KEY (cid)
);
insert records in "city" table
INSERT INTO city(cityname)

VALUES('Mul'),

('Lhr'),

('Mul'),

('Bhw'),

('Mul');
create table "courses" and insert records
CREATE TABLE courses(
    course_id INT NOT NULL AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (course_id)
);

INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');
SQL

Join Multiple Tables
SELECT * FROM personal p INNER JOIN city c
ON p.city = c.cid;

// All Tables data
SELECT * FROM personal p INNER JOIN city c
ON p.city = c.cid
INNER JOIN courses cr
ON p.courses = cr.course_id;

// Hide Extra colunms
SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname,cr.course_name 
FROM personal p INNER JOIN city c
ON p.city = c.cid
INNER JOIN courses cr
ON p.courses = cr.course_id;

SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname,cr.course_name 
FROM personal p INNER JOIN city c
ON p.city = c.cid
INNER JOIN courses cr
ON p.courses = cr.course_id
WHERE c.cityname = "Mul";