Lookup table

[Guest mpls51]

I have the following two table in oracle 9i:arc_book----------------------------------------------------------------------------------------------arc_id, arc_title, arc_publisher, arc_release_datesmc_book---------------------------------------------------------------------------------------------------------------------------------------------smc_id, smc_title, smc_publisher, smc_release_dateWhat I want to do is create a lookup table where I matchup the records that are the same book in the smc_book file and arc_book table.So the lookup table would look like:lookup-------------------------------------------------smc_id, arc_id, arc_title, arc_publisherWhen I match up books I don't want to re-add books that have already been added to the lookup table.Also when I am matching up the books in the smc_book and arc table I want to make sure it gets all similar book titles that have the same publisher and release date because the book title, publisher are not exactly the same in both table. For example with data:smc_book:smc_id smc_title smc_publisher smc_release_date---------------------------------------------------------------------------------------------------------------------------------------------1234 "Beautiful Wonder" "Wrox Books" 1/1/19992356 "Master PL/SQL" "OReilly Media" 6/5/20045432 "Harry Potter and Goblet of Fire" "Simon & Shuster" 2/4/2001arc_book:arc_id arc_title arc_publisher arc_release_date---------------------------------------------------------------------------------------------------------------------------------------------1245 "Wonder, Beautiful" "Wrox" 1/1/19991244 "The PL-SQL, Master" "Media, OReilly" 6/5/20044352 "Golbet of Fire, Harry Potter" "Simon and Shuster" 2/4/2001So I want to match up "Beautiful Wonder" in the smc_book table with "Wonder, Beautiful" in the arc_book table even though the title and publisher data is not exact.Could someone please give an example of how to match up "Beautiful Wonder" in the smc_book table with "Wonder, Beautiful" in the arc_book table even though the title and publisher data is not exact.Thanks

[justsomeguy]

There's not an easy way to do that. Especially not in SQL. You could get all of the records from the database and use another language to loop through them all and implement some sort of algorithm to match everything up based on what it thinks it should be, but the database server isn't going to be able to do that alone. This is the reason why each record needs a unique ID that you can store in other related tables, so if the IDs were the same in both tables then you know they match up even though the information might be a little different. If the information stored in the two tables is different and there isn't an existing ID that matches one to the other it's not going to be very easy to match things up.