Member Avatar for drewpee

Hi. I've made a record on a table which would be used as a comparison to other records in the table. for example..

table:
recordID 100001,Company Name
recordDI 999999,Company Name

i want to compare record 1000001's Company Name with record 9999999's Company name. How do I do that? thanks!

  • 3 Contributors
  • 3 Replies
  • 113 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by vb5prgrmr

Dani AI

Generated

asked how to compare two CompanyName values from the same table. : good call asking what DB is in use; the tags show MySQL and the OP mentioned ADO. pointed to set-based approaches for duplicates — that is useful when checking many rows. Below are two concrete, easy-to-run options: one does the comparison inside SQL (single query), the other does it in Visual Basic via ADO.

Self-join in SQL (MySQL-friendly). Normalize for case and stray spaces so small differences do not falsely show as mismatches:

SELECT t1.RecordID AS ID1,
       t1.CompanyName AS Name1,
       t2.RecordID AS ID2,
       t2.CompanyName AS Name2,
       CASE WHEN TRIM(LOWER(t1.CompanyName)) = TRIM(LOWER(t2.CompanyName))
            THEN 'Same' ELSE 'Different' END AS NameMatch
FROM MyTable AS t1
CROSS JOIN MyTable AS t2
WHERE t1.RecordID = 100001
  AND t2.RecordID = 999999;

ADO / Visual Basic approach (read two rows and compare in code). This is often simplest if only two records are needed or if fuzzy logic is required:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim name1 As String, name2 As String

Set cn = New ADODB.Connection
cn.Open connectionString

Set rs = cn.Execute("SELECT CompanyName FROM MyTable WHERE RecordID = 100001")
If Not rs.EOF Then name1 = rs!CompanyName
rs.Close

Set rs = cn.Execute("SELECT CompanyName FROM MyTable WHERE RecordID = 999999")
If Not rs.EOF Then name2 = rs!CompanyName
rs.Close

If StrComp(Trim(name1), Trim(name2), vbTextCompare) = 0 Then
    ' names match (case-insensitive)
Else
    ' names differ
End If

cn.Close

Notes and quick tips: MySQL default collation may already be case-insensitive; explicit LOWER/TRIM helps avoid surprises. For fuzzy matches (typos), consider SOUNDEX or a Levenshtein routine in VB. If the goal is discovering duplicates across the table, use a set-based query (as suggested) rather than row-by-row processing for better performance.

Recommended Answers

All 3 Replies

Member Avatar for debasisdas

by writing some SQL.

What is the database that you are working on ?

Member Avatar for drewpee

im using ADO db. and yeah iknow its by using sql.:( sarcasm hurts lol

Member Avatar for vb5prgrmr

Here are some SQL terms for you to look up...

Group By Clause with a Having Clause
Order By Clause

Now, there are several ways to accomplish what you want to do...

You can group by on the company name with a count of company name having that count > 1 so you only pull those company's that have duplicates, or you can order by company name and run through the resulting rs comparing previous record to current record and if a duplicate is found, then do whatever...

Good Luck

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.