CODEWITHSUNDEEP
mysqlsqlgroup-by
jeffkee
jeffkee

Reputation: 5238

Retrieve IDs of rows with same column fields in MySQL

I have an interesting situation - I have multiple properties on our system, some of them share the same address. So if the number column as well as the street column matches in value (I will be using LOWER() as a processor for the string columns) I want the IDs of these rows. ALSO I need to exclude rows that do not have matches.

The problem is that, as I understand it, if I use the GROUP BY command, it will not retrieve the IDs of each homes row that match the criteria.. What's my alternative? I could of course write a PHP script that stores all the address combos in an array and then pick out the duplicates to re-group the IDs, but I'm hoping there's a simpler way.

So far the MySQL query I have is

SELECT COUNT(*) as cnt, id FROM homes GROUP BY number, street WHERE cnt>1

But it does NOT work... I'm not sure if I'm using the cnt>1 part correctly.

EDIT:

Here's how my table looks like (desc homes result)

SQL query: desc homes; 
Rows: 72
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
masterid int(11) NO NULL
source int(11) NO MUL 0
firm_1 varchar(255) NO NULL
firm_2 varchar(255) NO NULL
firm_1_id varchar(255) NO MUL NULL
firm_2_id varchar(255) NO NULL
firm_phone_1 varchar(255) NO NULL
firm_phone_2 varchar(255) NO NULL
firm_fax_1 varchar(255) NO NULL
firm_fax_2 varchar(255) NO NULL
realtor_1 varchar(255) NO NULL
realtor_2 varchar(255) NO NULL
realtor_phone_1 varchar(255) NO NULL
realtor_phone_2 varchar(255) NO NULL
create_date datetime NO NULL
modify_date datetime NO NULL
lat varchar(255) NO MUL
long varchar(255) NO MUL
sold varchar(255) NO MUL
feature int(1) NO MUL 0
mls varchar(10) NO MUL
ord decimal(7,2) NO 0.00
type varchar(255) NO MUL 0
building varchar(255) NO
price varchar(255) NO
prevprice varchar(255) NO
showaddress enum('0','1') NO 1
unit varchar(255) NO
number varchar(255) NO
street varchar(255) NO
area varchar(255) NO MUL
city varchar(255) NO MUL
openhouse varchar(255) NO
videolink varchar(255) NO
youtube varchar(255) YES NULL
youtube1 varchar(255) YES NULL
remarks int(11) NO 0
bedroom varchar(255) NO
bathroom varchar(255) NO
sqftup varchar(255) NO
sqftmain varchar(255) NO
sqftdown varchar(255) NO
fullbath varchar(255) NO
halfbath varchar(255) NO
kitchen varchar(255) NO
fireplace varchar(255) NO
parking varchar(255) NO
storage varchar(255) NO
style varchar(255) NO NULL
tax varchar(255) NO
maintenance varchar(255) NO NULL
warranty varchar(255) NO NULL
sqft varchar(255) NO
lot varchar(255) NO
depth varchar(255) NO NULL
rearexposure varchar(255) NO NULL
basement varchar(255) NO NULL
suite varchar(255) NO NULL
view varchar(255) NO NULL
balcony varchar(255) NO NULL
rental varchar(255) NO NULL
construction varchar(255) NO NULL
frontage varchar(255) NO
story varchar(255) NO
year varchar(255) NO
description text NO NULL
description2 text NO NULL
dimension text NO NULL
heading varchar(255) NO
publish enum('0','1') NO MUL 0
views int(11) NO 0

Upvotes: 0

Views: 210

Answers (2)

Kyra
Kyra

Reputation: 5407

I am not 100% sure what you want but it sounds like you have duplicate houses in your database and you can tell if it is a duplicate if the 'number' and 'street' matches another row.

If so I would do this:

select id, unit, number, street 
from homes as h1
where number + '//' + street in 
    (select number + '//' + street from homes as h2 where h2.id <> h1.id and h1.city = h2.city)

The inner select statement returns all the rows (except the current one) as "number//street". This way you if the current row's "number//street" matches a value in the inner select statement it is a duplicate. I wasn't sure if id or masterid is your main id value at the end. The '//' is a unique string so you don't get the query thinking these two rows:

id       number         street
1        23             example
2        2              3example

are the same as it compares "23//example" with "2//example" instead of using "23example".

Upvotes: 0

dani herrera
dani herrera

Reputation: 51665

You can keep ids with mysql group_concat function agGregation:

SELECT 
  COUNT(*) as cnt, 
  group_concat( cast(Id as char) ) as ids 
FROM 
  homes 
GROUP BY 
  number, street 
HAVING
  COUNT(*) > 1

Upvotes: 4

Related Questions