have some problem with getting data in laravel
i have table companies and addresses, want to get all companies which have same address
an example
companies table
| 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 |
addresses table
| 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 |
i need companies with ids 1, 4 because they has same address
| id | name |
|---|---|
| 1 | company 1 |
| 4 | company 4 |
CodePudding user response：
What immediately comes to my head is using a SQL join to achieve this i.e using a SELF JOIN to compare the table with itself while also using subqueries to ensure the address information is available on the tables you're comparing. Please try below query out, should be of help:
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;