Home > database >  LARAVEL Get duplicated rows from several tables
LARAVEL Get duplicated rows from several tables

Time:02-01

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;

https://sqlize.online/s/dp

  • Related