Query showing unexpected results

[johnmerlino]

Hey all,

I had my query getting closer to being accurate until I had to make some additional changes. One change was if in our temp_query, the middle_name field had a value in it and therefore was not null, then we use the sql like to find like characters in the print_ready query that match somewhere in the field the first_name, last_name, and middle_initial of the temp_query (note that for the fields, there will usually be other names as well and hence I had to use Like statement). Else, if there was no middle initial, then we don't query the middle_name, since it does not exist for that specific record. In addition, I had to ignore certain characters during a selection so as not to confuse them with middle names. In order to ignore them when making a selection, I used the REPLACE(). Actually it returns less records than it should. It should return 80 and it's returning 66.

 SELECT print_ready.id, print_ready.names_1, print_ready.names_2, print_ready.addresses, print_ready.cities, print_ready.us_states_and_canada, print_ready.zip_codes
  FROM print_ready, temp_query
  WHERE (
  (
  REPLACE(REPLACE(REPLACE(print_ready.names_1,'LIV',''),'REV',''),'TR','')
  )
  and
  (
  print_ready.us_states_and_canada = "FL"
  or
  print_ready.us_states_and_canada = "NY"
  )  
 and
  IIF ((temp_query.middle_initial IS NOT NULL),
  (
  (
  print_ready.names_1 Like "*," & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " " & temp_query.middle_initial
  )
  and 
  (
  print_ready.names_1 Like temp_query.last_name & ",*" 
  or
  print_ready.names_1 Like temp_query.last_name & " *" 
  or
  print_ready.names_1 Like  "* " & temp_query.last_name & " *"
  )
  OR
  (
  print_ready.names_2 Like "*," & temp_query.first_name &  " " & temp_query.middle_initial & " *"
  or
  print_ready.names_2 Like "*," & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_2 Like "* " & temp_query.first_name & " " & temp_query.middle_initial
  )
  and 
  (
  print_ready.names_2 Like temp_query.last_name & ",*" 
  or
  print_ready.names_2 Like temp_query.last_name & " *" 
  or
  print_ready.names_2 Like  "* " & temp_query.last_name & " *"
  )
  ),
  (
  (
  print_ready.names_1 Like "*," & temp_query.first_name & " *"
  or
  print_ready.names_1 Like "*," & temp_query.first_name 
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " *"
  )
  and 
  (
  print_ready.names_1 Like temp_query.last_name & ",*" 
  or
  print_ready.names_1 Like temp_query.last_name & " *" 
  or
  print_ready.names_1 Like  "* " & temp_query.last_name & " *"
  )
  OR
  (
  print_ready.names_2 Like "*," & temp_query.first_name & " *"
  or
  print_ready.names_2 Like "*," & temp_query.first_name 
  or
  print_ready.names_2 Like "* " & temp_query.first_name & " *"
  )
  and 
  (
  print_ready.names_2 Like temp_query.last_name & ",*" 
  or
  print_ready.names_2 Like temp_query.last_name & " *" 
  or
  print_ready.names_2 Like  "* " & temp_query.last_name & " *"
  )
  )
  )
  );

Thanks for response.

[ajetrumpet]

john,

can you post small samples of what you're now getting and what you want to get. thanks! that'll get you a response pretty quickly.

[johnmerlino]

Thanks for reply. I can give a couple specific examples I noticed right away. It should be returning 80 records, but it's returning 66, which means the query fails for some records. Below are records that should have been returned but weren't:

This is how they look in temp_query:
last_name || first_name || middle_initial
Blair || Sheron || S
Brown || Wanda || R
Rodriguez || Lillian || M
Glaubman || Alan ||

I added the || here just to indicate a column break

This is how they appear in print_ready:
names_1
BLAIR,SHERON S
BROWN,BRENON I H/E BROWN,WANDA R
RODRIGUEZ,LILLIAN M
GLAUBMAN,ALAN & SHORSTEIN,LILLIAN

[johnmerlino]

There appears to be space between first and middle name.So that's not problem.

[ajetrumpet]

John,

Pay no attention to what is below. I made it readable. Are you aware that you're doing a cross join here? You should make this easier. Aren't these two tables joined somehow? If they're not, why aren't they? So if they're not, tell us how the data is similar between the two tables. Your query looks unnecessarily complex. I remember helping you on your last post, but this should be a lot easier than it is right now.

So what are the differences and similarities between the two tables? Given that, the query should be able to be simplified. For instance, if the tables are similar to a great degree, you can union the two instead of messing around with the cross join.

__________________________________________________ __________________________________________________ __


Records that do not appear (temp_ready):

BLAIR,SHERON S
BROWN,BRENON I H/E BROWN,WANDA R
RODRIGUEZ,LILLIAN M
GLAUBMAN,ALAN & SHORSTEIN,LILLIAN

Structure in (print_ready):

last_name || first_name || middle_initial
Blair || Sheron || S
Brown || Wanda || R
Rodriguez || Lillian || M
Glaubman || Alan ||

sql:

SELECT print_ready.id, print_ready.names_1, print_ready.names_2, print_ready.addresses, print_ready.cities, print_ready.us_states_and_canada, print_ready.zip_codes
  FROM print_ready, temp_query
  WHERE (
  (
  REPLACE(REPLACE(REPLACE(print_ready.names_1,'LIV',''),'REV',''),'TR','')
  )
  and
  (
  print_ready.us_states_and_canada = "FL"
  or
  print_ready.us_states_and_canada = "NY"
  )  
 and
  IIF ((temp_query.middle_initial IS NOT NULL),
  (
  (
  print_ready.names_1 Like "*," & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " " & temp_query.middle_initial
  )
  and 
  (
  print_ready.names_1 Like temp_query.last_name & ",*" 
  or
  print_ready.names_1 Like temp_query.last_name & " *" 
  or
  print_ready.names_1 Like  "* " & temp_query.last_name & " *"
  )
  OR
  (
  print_ready.names_2 Like "*," & temp_query.first_name &  " " & temp_query.middle_initial & " *"
  or
  print_ready.names_2 Like "*," & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_2 Like "* " & temp_query.first_name & " " & temp_query.middle_initial
  )
  and 
  (
  print_ready.names_2 Like temp_query.last_name & ",*" 
  or
  print_ready.names_2 Like temp_query.last_name & " *" 
  or
  print_ready.names_2 Like  "* " & temp_query.last_name & " *"
  )
  ),
  (
  (
  print_ready.names_1 Like "*," & temp_query.first_name & " *"
  or
  print_ready.names_1 Like "*," & temp_query.first_name 
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " *"
  )
  and 
  (
  print_ready.names_1 Like temp_query.last_name & ",*" 
  or
  print_ready.names_1 Like temp_query.last_name & " *" 
  or
  print_ready.names_1 Like  "* " & temp_query.last_name & " *"
  )
  OR
  (
  print_ready.names_2 Like "*," & temp_query.first_name & " *"
  or
  print_ready.names_2 Like "*," & temp_query.first_name 
  or
  print_ready.names_2 Like "* " & temp_query.first_name & " *"
  )
  and 
  (
  print_ready.names_2 Like temp_query.last_name & ",*" 
  or
  print_ready.names_2 Like temp_query.last_name & " *" 
  or
  print_ready.names_2 Like  "* " & temp_query.last_name & " *"
  )
  )
  )
  );

[johnmerlino]

temp_query is a query performed on a list of 200 records, which gathers the relevant first_name, last_name, and middle_initial.

print_ready is simply a query of 600,000 records. It has a names_1 field, names_2 field and address field. As shown in the example, names_1 can have multiple names. I'm only interested in querying for the names that are contained in the temp_query - the first_name, last_name, and middle_initial.

So the only relationship the two queries have is that I am searching on print_ready to find names that mirror the names of temp_query. A drawback is that print_ready can have multiple names in names_1 or names_2 so I use the like clause due to the fact that it won't always be an exact match.

Now the query I posted here is another query created in order to compare print_ready and temp_query in order to retrieve the records from print_ready whose names exist in temp_query.

[ajetrumpet]

John,

I know the way I would do this, but it is so overly complex at this point, see if my parsing abilities below can help you figure out why you're not getting the records you want first (before I mention the way I would go about it). I suspect that all the records aren't coming back because of unintended combinations in the IIF()'s sub sections of either 'TRUE' or 'FALSE'. Hopefully it is readable enough to help you figure it out on your own. Let me know how you get on (ignore the "+" signs - they are identifiers)...

Records that do not appear (temp_ready):

last_name || first_name || middle_initial
Blair || Sheron || S
Brown || Wanda || R
Rodriguez || Lillian || M
Glaubman || Alan ||

Structure in (print_ready):

BLAIR,SHERON S
BROWN,BRENON I H/E BROWN,WANDA R
RODRIGUEZ,LILLIAN M
GLAUBMAN,ALAN & SHORSTEIN,LILLIAN

sql:

SELECT print_ready.id, print_ready.names_1, print_ready.names_2, 

print_ready.addresses, print_ready.cities, 

print_ready.us_states_and_canada, print_ready.zip_codes

FROM print_ready, temp_query

  WHERE (
  
  +++++( //WHERE clause opening tag

  //Eliminates all 3 strings from records (Where is the comparison here?  
  It's missing the comparison... "WHERE ????? = 'replace() functions')
  REPLACE(REPLACE(REPLACE(print_ready.names_1,'LIV',''),'REV',''),'TR','') 
  )
  and //condition 1 where states are 'FL' or 'NY'
  (
  print_ready.us_states_and_canada = "FL"
  or
  print_ready.us_states_and_canada = "NY"
  )  
 and //condition 1 where states are 'FL' or 'NY' and 

  IIF +((temp_query.middle_initial IS NOT NULL), //IIF statement OPEN

  ++( //start of 'TRUE' value

  +++( //sub 1 of 'TRUE' 
  print_ready.names_1 Like "*," & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " " & temp_query.middle_initial
  +++) //end sub 1
  and 
  +++( //sub 2 of 'TRUE' 
  print_ready.names_1 Like temp_query.last_name & ",*" 
  or
  print_ready.names_1 Like temp_query.last_name & " *" 
  or
  print_ready.names_1 Like  "* " & temp_query.last_name & " *"
  +++) //end sub 2
  OR
  +++( //sub 3 of 'TRUE' 
  print_ready.names_2 Like "*," & temp_query.first_name &  " " & temp_query.middle_initial & " *"
  or
  print_ready.names_2 Like "*," & temp_query.first_name & " " & temp_query.middle_initial & " *"
  or
  print_ready.names_2 Like "* " & temp_query.first_name & " " & temp_query.middle_initial
  +++) //end sub 3
  and 
  +++( //sub 4 of 'TRUE' 
  print_ready.names_2 Like temp_query.last_name & ",*" 
  or
  print_ready.names_2 Like temp_query.last_name & " *" 
  or
  print_ready.names_2 Like  "* " & temp_query.last_name & " *"
  +++) //end sub 4

  ++), //end of 'TRUE' 

  ++( //start of 'FALSE' 

  +++( //sub 1 of 'FALSE' 
  print_ready.names_1 Like "*," & temp_query.first_name & " *"
  or
  print_ready.names_1 Like "*," & temp_query.first_name 
  or
  print_ready.names_1 Like "* " & temp_query.first_name & " *"
  +++) //end sub 1
  and 
  +++( //sub 2 of 'FALSE' 
  print_ready.names_1 Like temp_query.last_name & ",*" 
  or
  print_ready.names_1 Like temp_query.last_name & " *" 
  or
  print_ready.names_1 Like  "* " & temp_query.last_name & " *"
  +++) //end sub 2
  OR
  +++( //sub 3 of 'FALSE' 
  print_ready.names_2 Like "*," & temp_query.first_name & " *"
  or
  print_ready.names_2 Like "*," & temp_query.first_name 
  or
  print_ready.names_2 Like "* " & temp_query.first_name & " *"
  +++) //end sub 3
  and 
  +++( //sub 4 of 'FALSE' 
  print_ready.names_2 Like temp_query.last_name & ",*" 
  or
  print_ready.names_2 Like temp_query.last_name & " *" 
  or
  print_ready.names_2 Like  "* " & temp_query.last_name & " *"
  +++) //end sub 4
  
++) //end of 'FALSE' 

+) //IIF statement CLOSE

+++++) //WHERE clause ending tag

[johnmerlino]

Are you saying it's not iterating through the records so the if statement never returns true or false for a given record?
Something like:

for(var i = 0; i < temp_query.middle_initial.length; i++){
   if(temp_query.middle_initial[i] IS NOT NULL){
   .... 
  } 
}

But I presumed the iif function is already iterating through all the values of the field.

Other than that, as you mentioned, iif evaluates condition and then takes two paremeters, one for when condition returns true and one when it returns false: iif ( condition, value_if_true, value_if_false )

If field is not null and therefore contains a middle initial, we find record like first name and middle initial. Otherwise, we just search for the first and last name within the strring of the field. That's what I intend to happen, but obviously I must be overlooking something.

[ajetrumpet]

John,

this is not PHP and a query doesn't iterate through records. I did all of that commenting to help you solve your own problem. If you're still unsure of the reason why the query is not returning all the records, upload a sample and I'll help you out.

Note though, that this is quite complicated and I might not get to it right away.

[johnmerlino]

Thanks for response.

I attached a sample database. I didn't attach the original database because it's 200 mb. But the query I have been using is on this sample attachment and the only real difference is that here I am applying query to tables, where in my other version, I am applying the query to queries, where contacts and temp_table are queries.

[slave138]

The query looks like it may be more complicated than it needs to be. Have you considered writing a function to do a RegEx Replace and then calling it from the query? It gives you a lot more flexibility for pattern matching so you don't need to have so many near-redundant comparisons.

[johnmerlino]

The query looks like it may be more complicated than it needs to be. Have you considered writing a function to do a RegEx Replace and then calling it from the query? It gives you a lot more flexibility for pattern matching so you don't need to have so many near-redundant comparisons.

Well even if I use regexp, wouldn't it be performing what I'm doing above, which doesn't work fully? I'm not understanding why it's accounting for some names and not others, which pretty much meet the same criteria.

[slave138]

What you're doing above looks like a brute force solution to me using line after line of similar code to accomodate multiple possibilities. With every line there is the chance for a small mistake, an unexpected result, etc which is made that much harder to resolve because its buried in an SQL statement which would more accurately be described as an SQL essay.

With RegEx the filter might take a little tweaking but has a lot more wildcard options and should process faster because it doesn't have to iterate through 40 (I didn't count them so it might be more or less) possible statements looking for a match.

[weekend00]

Maybe this query is not the most complicated query that I've ever seen, but it must be one of the most complicated queries that I've ever seen!

I always created complicated codes some years ago. I've ever successfully used one line of code in FOXPRO to translate number to wording( e.g. 123 to "one hundred and twenty three").

but now not any more, I believe the simplest is the best.

[johnmerlino]

Am I moving in right direction here?

CREATE FUNCTION RegExpLookup (param1 varchar(255), param2 varchar(255), param3 varchar(255)) RETURNS VARCHAR(255)  
 
 params3 = params3 || "";
 
  RETURN param1 REGEXP '/[temp_table.last_name]|[temp_table.last_name] |[temp_table.last_name],/g'; 
  RETURN param2 REGEXP '/,[temp_table.first_name] | [temp_table.first_name] /g'; 
  RETURN param3 REGEXP '/[temp_table.middle_initial]/g'; 
 
SELECT print_ready.id, print_ready.names_1, print_ready.names_2, print_ready.addresses, print_ready.cities, print_ready.us_states_and_canada, print_ready.zip_codes
FROM print_ready, temp_table
WHERE (
print_ready.us_states_and_canada = "FL"
or
print_ready.us_states_and_canada = "NY"
and
IIF ((temp_table.middle_initial IS NOT NULL),
print_ready.names_1 Like RegExpLookup(temp_table.last_name, temp_table.first_name,  temp_table.middle_initial),
print_ready.names_1 Like RegExpLookup(temp_table.last_name, temp_table.first_name)
)
;

I can't even save this. Syntax error it says. I don't think I'm passing arguments correctly and the function declaration syntax appears to be off.