SQL/MySQL How to query 3 tables with or wihtout a join

John D K -

You can query more than 2 tables with SQL. Below you can find examples how to query 3 tables with join and without a join:

You can find a video tutorial here: SQL select from multiple tables(two and more).

SQL query 3 table no join

For the beginners using joins could be a bit difficult in the beginning. I recommend to start working with joins once you are confident with SQL. Below you can see how to query 3 related table by their foreign keys without using a join.

SELECT *
FROM girls B, couple C, boys A
where C.boy = A.id
AND C.girl = B.id

SQL query 3 table with join

This example is better for more advanced users who has good experience with SQL and DB. Table couple is joined with INNER JOIN to two other tables: boys and girls with this code syntax:

SELECT *
FROM couple d
INNER JOIN boys a ON d.boy = a.id
INNER JOIN girls b ON b.id = d.girl

Resources:

More examples and explanation here:

  • mysql join simple example for beginners

SQL joins for beginners

  • Frequent SQL commands select

Most frequent SQL commands

Tables for the examples:

The tables for creation and the data:

CREATE TABLE `boys` (
	`id` INT(11) NULL DEFAULT NULL,
	`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
	`relation` INT(11) NULL DEFAULT NULL,
	INDEX `FK_boys_girls` (`relation`),
	CONSTRAINT `FK_boys_girls` FOREIGN KEY (`relation`) REFERENCES `girls` (`id`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

CREATE TABLE `couple` (
	`boy` INT(11) NULL DEFAULT NULL,
	`girl` INT(11) NULL DEFAULT NULL,
	`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin'
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;


CREATE TABLE `girls` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
AUTO_INCREMENT=6
;


INSERT INTO `boys` (`id`, `name`, `relation`) VALUES (1, 'Brady', 5);
INSERT INTO `boys` (`id`, `name`, `relation`) VALUES (2, 'Mike', 1);
INSERT INTO `boys` (`id`, `name`, `relation`) VALUES (3, 'Alex', 3);
INSERT INTO `boys` (`id`, `name`, `relation`) VALUES (4, 'Daniel', NULL);
INSERT INTO `boys` (`id`, `name`, `relation`) VALUES (5, 'James', NULL);

INSERT INTO `couple` (`boy`, `girl`, `name`) VALUES (1, 1, 'Nice');
INSERT INTO `couple` (`boy`, `girl`, `name`) VALUES (2, 2, 'Funny');
INSERT INTO `couple` (`boy`, `girl`, `name`) VALUES (3, 4, 'NoSQL');


INSERT INTO `girls` (`name`) VALUES ('Emma');
INSERT INTO `girls` (`name`) VALUES ('Ann');
INSERT INTO `girls` (`name`) VALUES ('Kim');
INSERT INTO `girls` (`name`) VALUES ('Olivia');
INSERT INTO `girls` (`name`) VALUES ('Victoria');