SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear