sql query help

[TheDude05]

I'm not sure how to do this with a sql query.

I have a table that contains "duplicate" records. (I quote duplicate because they are not true duplicates)

Heres the issue, i have a table that looks like this...

+----+--------+-------+----------+-------------+
| id | folder | block | place_id | description |
+----+--------+-------+----------+-------------+

So say for example I have a dataset like so..

1 | 2345 | 2 | 2 | lot 4
2 | 2345 | 2 | 2 | lot 5
3 | 7349 | 6 | 7 | lots 3,4

I want a query that will return the first two records because folder,block,place_id as a whole exist more than once in the database. (hope that makes sense)

Heres the background behind my problem. A client had a large set of paper documents that kept track of the work they had done for a specific piece of property. Well say that did a piece of work on Lot 4 and then later on Lot 5, they never updated their initial record to say "Lots 4,5". They instead made a new entry with "Lot 5". So when they started adding data to the SQL database, they did it the same way it was on paper. So there are a lot of entries in the database that should consolidated into one record but haven't because of the way the data was entered. Thus i need this query to see which set of records need updated/deleted.

Any help would be appreciated. I tried using the concat function on those 3 fields and then counting that new concat field but it wont allow me. (Says it cant find the field) This is on a mysql 5.0 server.

 

[piako]

Sounds like you need to do some type of self join.

Select T1.folder, T2.folder, T1.block, T2.block, T1.place_id, T2.place_id
from Table T1, Table T2
where t1.folder = t2.folder AND t1.block = t2.block AND t1.place_id = t2.place_id;

you're welcome. hope that can execute in mysql. ;p

 

[mikeblas]

You might want to use less-than to compare keys instead of equals, as equals will give two rows per duplicate key.

 

[TheDude05]

Thanks for the help from both of you. This is the query i have for the time being

SELECT T1.id, T2.id, T1.caption, T2.caption ,T1.folder, T2.folder, T1.block, T2.block, T1.place_id, T2.place_id
FROM records T1, records T2
WHERE T1.folder = T2.folder AND T1.block = T2.block AND T1.place_id = T2.place_id AND T1.id < T2.id
ORDER BY T1.place_id, T1.id, T2.id DESC

It works pretty well except when there is more than 2 "duplicate" rows. Like for this condition here:

+-----+-----+-----------------------+-----------------------+--------+--------+-------+-------+----------+----------+
| id  | id  | caption               | caption               | folder | folder | block | block | place_id | place_id |
+-----+-----+-----------------------+-----------------------+--------+--------+-------+-------+----------+----------+
| 111 | 114 | Lots 12 & 13          | Lot 9                 | 5231   | 5231   | 13    | 13    |        8 |        8 |
| 111 | 112 | Lots 12 & 13          | Lots 10, 11, 12, & 13 | 5231   | 5231   | 13    | 13    |        8 |        8 |
| 112 | 114 | Lots 10, 11, 12, & 13 | Lot 9                 | 5231   | 5231   | 13    | 13    |        8 |        8 |
+-----+-----+-----------------------+-----------------------+--------+--------+-------+-------+----------+----------+

However its not that big of a deal. If you know of a simple way to eliminate the condition all I'm ears, but if not don't worry about it. This is a one time query that I'm using to help me fix the records by hand, doesn't need to be 100% perfect.

 

[mikeblas]

It works pretty well except when there is more than 2 "duplicate" rows. Like for this condition here:

I can't guess what you think is wrong with those results. (Remember, I don't have access to your database.) What is that you'd rather have returned?

 

[TheDude05]

The only thing would be nicer about the current query is say for example on the data above, do not return the row that has the ids of 112 & 114 because I already know they are duplicates based off of the first two records (ids 111,112 & 111,114)

Originally my plan was to just buck up and go through phpmyadmin by hand and fix one of the records and then delete the others. But now I think it might be easier for me to write a script that will just insert a new record based off of the data from the old duplicate records.

Take for example the resultset I posted last. My script will see that folder, block, and place_id are all the same. Now insert a new record with the same folder, block, place_id but with a new caption that is somehow based off the duplicates and then delete the old records.

Whether I can regex the caption field I don't know (I'm a newb when it comes to regular expressions ) But regardless if it takes user input or regular expressions, that may be much less time consuming than doing it by hand.


The current query I have now should work, I just need to figure out my program logic. You guys are much better programmers than I am, any suggestions?


Also, if it helps i can post a dump of the table in question. (Its nothing secretive )


UPDATE:

Ok so I think I found a way to do this easily. Its really inefficient but its only going to be ran once anyways so I dont mind.

First I run this query:

SELECT DISTINCT CONCAT_WS('--',T1.folder,T1.block,T1.place_id)
FROM records T1, records T2
WHERE T1.folder = T2.folder AND T1.block = T2.block AND T1.place_id = T2.place_id AND T1.id < T2.id
ORDER BY T1.folder ASC

This returns one field with every folder+block+place_id, split by a '--', that has more than one record. It returns roughly 1000 rows.

Now my script will run through this script, split the string, and run a query that returns the caption and id fields with the given folder, block, place_id. Then i'll combine the caption fields into a string (not sure how yet) and re-insert a new record. Finally, delete the old records.



Did I overlook anything?