Correct syntax for string comparison in SQL?

[seasonedweb]

Hello

Sorry I think I may have asked this question in the past but now this has become a comedy of errors.

Simply put, there are two tables in the same DB containing rows of individuals last names, first, DOB etc,

Table 1 has 3000 records

Table 2 has 4000 in which the above 3000 are intermingled with no other matching identity except last name.

I need the query to pull out the 1000 names that were added.

No success yet on several approaches. Last names are nvarchar max, SQL Server.

<cfquery name="find_added_lastnames" datasource="#Request.BaseDSN#">

Select lastname1
from TABLE1
join TABLE2
on ' '+ lastname1+' ' not like '% '+ lastname2+' %'
ORDER BY lastname2 ASC

<cfquery name="find_added_lastnames" datasource="#Request.BaseDSN#">

SELECT lastname1, lastname2
FROM table1, table2
WHERE lastname1 NOT LIKE '%lastname2%'
ORDER BY lastname2 ASC
</cfquery>

<cfquery name="find_added_lastnames" datasource="#Request.BaseDSN#">

SELECT lastname1, lastname2
FROM table1, table2
WHERE lastname1 NOT LIKE lastname2 + '%'
ORDER BY lastname2 ASC
</cfquery>

Actually running the above query works with “LIKE” vs “NOT LIKE” returns- New Last Names: 14388 (cartesian product).

Plus several iterations of these.

Any help much appreciated.

Thank You- Norman B.

[BKBK]

There is a simple solution I can think of. Select from Table2, using Table1 to filter by lastname.

That is, 

SELECT firstname, lastname, dob, etc
FROM table2
WHERE lastname NOT IN (SELECT lastname FROM table1)

[seasonedweb]

BKBK Thank you so much for your help all these years! I'm 72 now & neen to finally keep a DB with all the queries that address what I often encounter- I'm still working on it.

So this query using NOT IN:

SELECT ged_lastname_utf, ged_firstname_utf
FROM gedcomutf8
WHERE ged_lastname_utf NOT IN

(SELECT ged_lastname, ged_firstname
FROM ged_main
)

ORDER BY ged_lastname ASC

Results:

][SQLServer]Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Thanks again!

[seasonedweb]

taking out , ged_firstname errors

Invalid column name 'ged_lastname' think I have it backwards

[seasonedweb]

I do want to check both first and last names however.

[seasonedweb]

Sorry for all theres posts -this query does not error:

SELECT ged_lastname_utf, ged_firstname_utf
FROM gedcomutf8
WHERE ged_lastname_utf NOT IN

(SELECT ged_lastname
FROM ged_main
)

ORDER BY ged_lastname_utf ASC

But now returns 0 records which I know can't be true.

[Dave Watts]

Don't be so quick to assume it has to be true! Go to your SQL query tool and run this. You can run it in pieces if you need to - for example, run "SELECT ged_lastname FROM ged_main" first. You can simplify that by removing duplicate entries:

SELECT DISTINCT ged_lastname

FROM ged_main

ORDER BY ged_lastname

Then, run something like this:

SELECT DISTINCT ged_lastname_utf

FROM gedcomutf8

ORDER BY ged_lastname_utf

... and manually see if you have any overlap between the two lists.

Dave Watts, Eidolon LLC

[seasonedweb]

Dave thanks again for your help.

The table gedcomutf8 has about 4000 records.

The table ged_main has about 3000.

There is no PK in gedcomutf8, so the only columns that have anything close to a matching identity are last names & first names. 60% have multiple first names and even more have multiple last names separated by 'dit', ',' and or '/'.

I want to pull the names of the1000 added records contained in gedcomutf8 that do not match the ones in ged_main.

Sorry I've been stuggling with this for while. I have no choice but the deal with the new added records this way.

That's why I'm sure that the query will return like at least 800.

[BKBK · Accepted solution]

Did you do the test that Dave suggested? If so, what was the result?

A suggestion for the test:

1.  Create and launch a CFM page containing the following code.
2.  Do any of the names in both columns match?

<cfquery name="mainQ" datasource="name_of_your_datasource">
SELECT DISTINCT ged_lastname
FROM ged_main
ORDER BY ged_lastname
</cfquery>

<cfquery name="comutf8Q" datasource="name_of_your_datasource">
SELECT DISTINCT ged_lastname_utf
FROM gedcomutf8
ORDER BY ged_lastname_utf
</cfquery>

<p>
	<strong>
		Number of ged_main records: <cfoutput>#mainQ.recordcount#</cfoutput> 
	</strong>
</p>
<p>
	<strong>
		Number of gedcomutf8 records: <cfoutput>#comutf8Q.recordcount#</cfoutput> 
	</strong>
</p>

<cfset maxRecordcount=max(mainQ.recordcount, comutf8Q.recordcount)>

<!---
Loop through both queries simultaneously.
Output the respective lastname-rows from ged_main and getcomutf8 side by side in a table.
 --->
<cfoutput >
<table border="1">
	<cfloop from="1" to="#maxRecordcount#" index="n">
		<tr>
		<td >
			<cfif n lte mainQ.recordcount>#mainQ.ged_lastname[n]#<cfelse>&nbsp;</cfif>
		</td>
		<td >
			<cfif n lte comutf8Q.recordcount>#comutf8Q.ged_lastname_utf[n]#<cfelse>&nbsp;</cfif>
		</td>
		</tr>
	</cfloop>	
</table>
</cfoutput>

[seasonedweb]

Hello- this query works great-

The results are:

Do any of the names in both columns match?  

Number of ged_main records: 3396

Number of gedcomutf8 records: 3770

which indicates, if I am understanding this, that there are 374 distinct last names that are in gedcomutf8 that are not in ged_main. I can really work with this now and take it from here- much appreciated!

[seasonedweb]

Hello I'm still not able to read the results if you want to see it's here:

https://chicopeeclan.net/bkbk-1.cfm

[seasonedweb]

This is using last names only just as you sent:

https://chicopeeclan.net/bkbk-2.cfm

Also the ged_main table has a PK  but the gedcomutf8 does not but has a column of unique values that came with the CSV.

[seasonedweb]

Hello again

My ultimate goal is to add the new entries from gedcomutf8 to ged_main.

If that could be accomplished easier? Say

INSERT/SLECT?

insert into ged_main.

select lastname firstmame from gedcomutf8 

where gedcomutf8 lastname firstmame NOT LIKE ged_main lastname firstmame ?

[seasonedweb · Accepted solution]

I think I did it- and I appreciate all the help from BKBK & Dave Watts!

I successfully added 147 new records to the main DB that did not match first & last names already there.

I used:

INSERT INTO a

VALUES ( b.lastname, b.firstname

WHERE NOT EXISTS

(SELECT a.lastname, a.firstname
FROM a
WHERE a.lastname =  b.lastname 
AND a.firstname =  b.firstname 

)