CREATE TABLE companies (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE addresses (
id INT NOT NULL,
company_id INT NOT NULL,
a1 VARCHAR(50) NOT NULL,
a2 VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL,
state VARCHAR(50) NOT NULL,
PRIMARY KEY(id),
CONSTRAINT FK_CompanyAddress FOREIGN KEY (company_id)
REFERENCES companies(id)
);
INSERT INTO companies (id, name) VALUES
(1, 'company 1'),
(2, 'company 2'),
(3, 'company 3'),
(4, 'company 4');
INSERT INTO addresses (id, company_id, a1, a2, city, state) VALUES
(1, 1, 'aa1', 'aa1', 'cc1', 'ss1'),
(2, 2, 'aa2', 'aa2', 'cc2', 'ss2'),
(3, 3, 'aa3', 'aa3', 'cc3', 'ss3'),
(4, 4, 'aa1', 'aa1', 'cc1', 'ss1');
SELECT t2.c2_id as id, t2.c2_name as name
FROM (
SELECT c.id as c1_id, c.name as c1_name, CONCAT(addresses.a1, ' ', addresses.a2, ' ', addresses.city, ' ', addresses.state) as c1_address
FROM companies c
JOIN addresses
ON c.id = addresses.company_id
) t1,
(
SELECT c.id as c2_id, c.name as c2_name, CONCAT(addresses.a1, ' ', addresses.a2, ' ', addresses.city, ' ', addresses.state) as c2_address
FROM companies c
JOIN addresses
ON c.id = addresses.company_id
) as t2
WHERE c1_id <> c2_id
AND c1_address = c2_address;
Telegram: DB developers channel