我在获取LALAVEL中的数据时遇到一些问题
我有companies号桌和addresses号桌，想要得到所有地址相同的公司
一个例子
公司表
| id | name | created_at |
|---|---|---|
| 1 | company 1 | 30/01/2023 |
| 2 | company 2 | 30/01/2023 |
| 3 | company 3 | 30/01/2023 |
| 4 | company 4 | 30/01/2023 |
地址表
| id | company_id | a1 | a2 | city | state |
|---|---|---|---|---|---|
| 1 | 1 | aa1 | aa1 | cc1 | ss1 |
| 2 | 2 | aa2 | aa2 | cc2 | ss2 |
| 3 | 3 | aa3 | aa3 | cc3 | ss3 |
| 4 | 4 | aa1 | aa1 | cc1 | ss1 |
我需要ID为1、4的公司，因为它们的地址相同
| id | name |
|---|---|
| 1 | company 1 |
| 4 | company 4 |
我马上想到的是使用SQL联接来实现这一点，即使用自联接将表与其自身进行比较，同时使用子查询来确保地址信息在要比较的表上可用.请try 下面的查询，应该会有所帮助:
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;