duplicate query troubles

Results 1 to 2 of 2

Thread: duplicate query troubles

  1. #1
    Join Date
    Dec 1969

    Default duplicate query troubles

    hello,<BR>I have a database with a list of Music venues. each record as 5 fields to contain the name, one is the correct name and spelling, the 4 others are alternate names that are frequently used. here&#039;s what I want to do:<BR><BR>when a user (through a web interface) attempts to add a venue, I&#039;d like to check the user entered venue name against all 5 name possibilities for each record in the database for duplicates. I want to consider a duplicate to be anything that contains the string entered by the user, in other words, if the user enters "Cary Street", I want "The Cary Street Cafe" to be considered a duplicate. I then want to place all dups into a recordset and then display the record set to the user. <BR><BR>displaying the record set is no problem, but its unclear to me how to construct such a query. any help??<BR><BR>Thanks so much,<BR>Ryan<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Best to change DB design...

    Instead of having 5 fields in the record, add another table--the "Addresses" table, perhaps--which now allows you to have as many addresses per venue as you want. The table is simple:<BR> venueID : foreign key to Venues table<BR> venueAddress : the address<BR><BR>Now it&#039;s easy:<BR><BR>SELECT Venues.* <BR>FROM Venues, Addresses<BR>WHERE Venues.venueID = Addresses.venueID<BR>AND venueAddress LIKE &#039;%Cary Street%&#039;<BR><BR>Presto.<BR><BR>Incidentally, this technique is called "Database Normalization." Look it up.<BR><BR>******************<BR><BR>If you insist on leaving the DB alone, you&#039;ll have to do<BR><BR>SELECT * <BR>FROM Venues<BR>WHERE address1 LIKE &#039;%Cary Street%&#039;<BR>OR address2 LIKE &#039;%Cary Street%&#039;<BR>OR address3 LIKE &#039;%Cary Street%&#039;<BR>OR address4 LIKE &#039;%Cary Street%&#039;<BR>OR address5 LIKE &#039;%Cary Street%&#039;<BR><BR>Ugh.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts