• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Correct syntax for string comparison in SQL?

Participant ,
Feb 12, 2023 Feb 12, 2023

Copy link to clipboard

Copied

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.

Views

717

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 2 Correct answers

Community Expert , Feb 16, 2023 Feb 16, 2023

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>
	<
...

Votes

Translate

Translate
Participant , Feb 16, 2023 Feb 16, 2023

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 

)

Votes

Translate

Translate
Community Expert ,
Feb 15, 2023 Feb 15, 2023

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 15, 2023 Feb 15, 2023

Copy link to clipboard

Copied

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!

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 15, 2023 Feb 15, 2023

Copy link to clipboard

Copied

taking out , ged_firstname errors

 
Invalid column name 'ged_lastname' think I have it backwards
 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 15, 2023 Feb 15, 2023

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 15, 2023 Feb 15, 2023

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 15, 2023 Feb 15, 2023

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 15, 2023 Feb 15, 2023

Copy link to clipboard

Copied

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.

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 16, 2023 Feb 16, 2023

Copy link to clipboard

Copied

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>

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 16, 2023 Feb 16, 2023

Copy link to clipboard

Copied

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!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 16, 2023 Feb 16, 2023

Copy link to clipboard

Copied

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

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 16, 2023 Feb 16, 2023

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 16, 2023 Feb 16, 2023

Copy link to clipboard

Copied

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 ?

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 16, 2023 Feb 16, 2023

Copy link to clipboard

Copied

LATEST

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 

)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation