Hi all,
I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) I want to select distinct() and
join on id number to return a recordset containing every individual
listed in both the files HOWEVER, in some cases an incomplete ID
number has been collected into one of the 2 files -is there a way to
join on partial matches not just identical records in the same way as
you can select where LIKE '%blah, blah%'??
Is hash joining an option i should investigate?
TIA
Mark 4 6185
A join expression can include any predicates, including LIKE:
....
ON A.colx LIKE B.colx+'%'
You may also find the functions CHARINDEX and PATINDEX useful (see BOL).
--
David Portas
------------
Please reply only to the newsgroup
--
"Mark" <ma**@compuchem.co.za> wrote in message
news:63**************************@posting.google.c om... Hi all, I have 2 files containing Id numbers and surnames (these files essentially contain the same data) I want to select distinct() and join on id number to return a recordset containing every individual listed in both the files HOWEVER, in some cases an incomplete ID number has been collected into one of the 2 files -is there a way to join on partial matches not just identical records in the same way as you can select where LIKE '%blah, blah%'?? Is hash joining an option i should investigate?
TIA Mark
A join expression can include any predicates, including LIKE:
....
ON A.colx LIKE B.colx+'%'
You may also find the functions CHARINDEX and PATINDEX useful (see BOL).
--
David Portas
------------
Please reply only to the newsgroup
--
"Mark" <ma**@compuchem.co.za> wrote in message
news:63**************************@posting.google.c om... Hi all, I have 2 files containing Id numbers and surnames (these files essentially contain the same data) I want to select distinct() and join on id number to return a recordset containing every individual listed in both the files HOWEVER, in some cases an incomplete ID number has been collected into one of the 2 files -is there a way to join on partial matches not just identical records in the same way as you can select where LIKE '%blah, blah%'?? Is hash joining an option i should investigate?
TIA Mark
>> I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) <<
Since these are files and not tables, as you just said, why not use a
file difference utility? Now if you mean that you have tables, then
we can give you a query. Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema are. Sample data is also a
good idea, along with clear specifications -- what does "partial
match" mean?? In Full SQL-92, that is a reserved word with a definite
meaning.
>> I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) <<
Since these are files and not tables, as you just said, why not use a
file difference utility? Now if you mean that you have tables, then
we can give you a query. Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema are. Sample data is also a
good idea, along with clear specifications -- what does "partial
match" mean?? In Full SQL-92, that is a reserved word with a definite
meaning. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mark |
last post by:
Hi all,
I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) I want to select distinct() and
join on id number to return a recordset containing every...
|
by: nospam |
last post by:
Ok, I asked this question before and I also looked at the book "First Look
at ASP.NET 2.0"
I also read Paul wilson's web page explanation. HOWEVER......
The book and that web page talks about...
|
by: Jon Slaughter |
last post by:
struct NullClass { void Null() { } };
template <unsigned int i, typename T>
struct Node
{
enum {I = i};
typedef T Class;
};
|
by: Erik Wikström |
last post by:
In school (no I will not ask you to do my schoolwork for me) we talked
about policy-based design and got an assignment where we got the a code-
fragment from a stack-implementation. The idea with...
|
by: Larry |
last post by:
I have the following code attached to a search button on a form that runs a
query. It works great, except that the search for Last Name only returns
exact matches. It is even case sensitive....
|
by: veaux |
last post by:
Question about joins in queries. I have 2 tables with a field called
"ID".
Table 1
Rec1 = Jan12FredFlintstone
Rec2 = Feb01WilmaRubble
Table 2
Rec1 = Jan12BarneyRubble
Rec2 = Mar03SamSlate
|
by: Sami Rehman |
last post by:
Hi
I want to use namespaces to organize all the classes, web forms, and user
controls. It works fine with my user defined classes, but when I try to put
all the web pages in a namespace for...
|
by: gyap88 |
last post by:
I m currently creating a search engine for my database using visual basic 2005.
I want to my program to return search result even if the user only enter PARTIAL matches.
Dim city As String...
|
by: chungiemo |
last post by:
Hi
thought I would do another thread as this one is a bit different from the previous problem
I am looking for a solution to the relating problem
Comparing 2 access databases with 2 tables,...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |