MySQL query to list non duplicate items

poisa

I have two tables. Let's call them "source" and "target". I need to read every field name from "source", do some processing to it, and then copy it to "target". The reason I have to copy fields from one table to the other is because the processing is prone to be interrupted by the user, and I need to know what got done and be able to resume it on request.

Here is where I'm stuck. I need a query that will compare both tables, and only return those rows on the "source" table that have not been processed yet.

Bare in mind that:
1) I cannot modify the "source" table at all.
2) I can do anything I need to the "target" table.
3) The "source" table holds around 100,000 rows.

I have tried all the table joins that my limited MySQL knowledge could come up with but all I ended up with was duplicate results. 😕

Any help is much appreciated...

xblue

Originally posted by poisa
I have tried all the table joins

what did you try? doesn't sound that impossible to me as of now, I'd go for a left join making use of null values being returned if a record from one table cannot be found in the other.

poisa

xblue, I'm not trying to be rude here but when I said "I have tried all the table joins", the sentence continued "that my limited MySQL knowledge could come up with", being LIMITED a key word.

I also never said this was impossible. I just said that I was stuck.

If this is simple to you, please post some SQL so that I (and future forum readers who might stumble on this one) have something tangible to work with. Even though I understand what you propose, I have no means to turn that into a query.

xblue

Originally posted by poisa
xblue, I'm not trying to be rude here

no, me neither. I do like this forum very much for the friendliness and extraordinary patience that is common around here.

Originally posted by poisa
have something tangible to work with

this is not far from what I meant. it often helps those folks who are trying to help you if you post your approach even if it does not work yet.

ok, back to your query. there is a good example in the manual:

"You can use this fact to find records in a table that have no counterpart in another table:

mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;"

http://dev.mysql.com/doc/mysql/en/join.html

poisa

xblue, your query works nicely and performs well too. In the meantime I had already found this one:

SELECT DISTINCT s.name
FROM source s
LEFT OUTER JOIN target t
ON s.id = t.id
WHERE t.id IS NULL

Although this one works too, it takes at least 30 more seconds to execute so I'll stick with the one you provided.

As far as the mysql manual, you are right, I should have tried that query. The reason that I didn't try it was because of what's written right before the sentence you quoted "If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table"
I though I was going to get a bunch of NULLs in the result set which I was going to have to deal with at the PHP level, and I was looking for a "tidier" solution.

To be quite honest, I still need to get the hang of joins. My MySQL certification book should be here in about a week 🙂