Joining on partial matches

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
MarkA 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" <mark@.compuchem.co.za> wrote in message
news:632892db.0310290405.4a0e06bd@.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" <mark@.compuchem.co.za> wrote in message
news:632892db.0310290405.4a0e06bd@.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.

Joining multiple tables

I need your help on this. I am not getting the results i wanted in this script.

select distinct s.firstname, s.lastname, c[coursename],(midterm+final+assignment) as "total mark"

from student as s join studentrecord as sr

on (s.student#=sr.student#)

join course as c on (sr.course#=c.course#)

join courseenrollment as ce on (s.student#=ce.student#)

where ce.[status]=1

Now the results should only pick up 10 students who are enrolled (status=1) but the program is bypassing the where clause and returns all the results even though students were enrolled or not. Let me kknow your thoughts.

Join logic is applied in order, so if you rewrite your query like this it should provide the results expected.

1st, join the student and enrollment table on the student# using only the records with a status = 1 - this will return only students

2nd, join the student record table by the student# - this joins to the resultset of the 1st join

3rd, join the course table to the resultset of the previous joins by course# to the studentrecord table AND to the enrollment table.

Code Snippet

SELECT DISTINCT s.firstname, s.lastname, c.coursename,(midterm+final+assignment) as [total mark]

FROM student s INNER JOIN courseenrollment ce on ce.[student#] = s.[student#] AND ce.status = 1

INNER JOIN studentrecord sr on s.[student#] = sr.[student#]

INNER JOIN course c on c.[course#] = sr.[course#] AND c.[course#] = ce.course#]

Try this, and repost if it doesn't work or the assumption I made about the courseenrollment table having a course# field is incorrect.

|||

This is unexpected but logically true. If you add ce.status to the SELECT portion, I'm sure you'll see that the status = 1 for all records

Adamus

Joining multiple tables

I need your help on this. I am not getting the results i wanted in this script.

select distinct s.firstname, s.lastname, c[coursename],(midterm+final+assignment) as "total mark"

from student as s join studentrecord as sr

on (s.student#=sr.student#)

join course as c on (sr.course#=c.course#)

join courseenrollment as ce on (s.student#=ce.student#)

where ce.[status]=1

Now the results should only pick up 10 students who are enrolled (status=1) but the program is bypassing the where clause and returns all the results even though students were enrolled or not. Let me kknow your thoughts.

Join logic is applied in order, so if you rewrite your query like this it should provide the results expected.

1st, join the student and enrollment table on the student# using only the records with a status = 1 - this will return only students

2nd, join the student record table by the student# - this joins to the resultset of the 1st join

3rd, join the course table to the resultset of the previous joins by course# to the studentrecord table AND to the enrollment table.

Code Snippet

SELECT DISTINCT s.firstname, s.lastname, c.coursename,(midterm+final+assignment) as [total mark]

FROM student s INNER JOIN courseenrollment ce on ce.[student#] = s.[student#] AND ce.status = 1

INNER JOIN studentrecord sr on s.[student#] = sr.[student#]

INNER JOIN course c on c.[course#] = sr.[course#] AND c.[course#] = ce.course#]

Try this, and repost if it doesn't work or the assumption I made about the courseenrollment table having a course# field is incorrect.

|||

This is unexpected but logically true. If you add ce.status to the SELECT portion, I'm sure you'll see that the status = 1 for all records

Adamus

Join on subquery with distinct?

Can someone give me some reasonable explanation why these two queries are performing dramatically different:

First (slow):

select
tableA.column1,
tableA.column2
from
tableA join (select distinct col1,col2,col3 from myView) vw
on vw.col1=tableA.col1 and vw.col2=tableA.col2 and vw.col3=tableA.col3

Second (fast):

create table #tempTable (col1 varchar(200),col2 int,col3 varchar(200))

insert into #tempTable
select distinct col1,col2,col3 from myView

select

tableA.column1,

tableA.column2

from

tableA join #tempTable vw

on vw.col1=tableA.col1 and vw.col2=tableA.col2 and vw.col3=tableA.col3

SQL Server 2005 Express
Win2K3 Server

Regards,
Marko Simic

Marko:

I can't really mock this without knowing how the view is structured. It is not possible to mock a query plan that involves a view without knowing the components of the view. Can you give details of the makeup of the view?

Dave

|||

Can you post the plan for your queries, such as:

set showplan_text on
--set statistics profile on
go

--your query--

go
--set statistics profile off
set showplan_text off

The showplan one gives you estimated, the profile one gives you actual plan. That will be very helpful in this.

|||

When I executed queries (for generating estimated and actual plans), suddenly problematic one executed much faster.
Most probably, problem was about server's hardware performance, like insufficient RAM, at the moment of query execution.
My assumption is based on the fact that problematic query is using subquery, which by many views, is using more memory then query with temp table.

Anyway, I am sending you a link to XLS file with all statistics (of query with subquery) you asked, in case that something else could cause strange behavior and can be seen from this.

http://139.142.50.130/test/statistics.xls

Thank you for your efforts

|||

Without both to compare it is really impossible to say too confidently, but there are a couple of really large looking table scans (Clustered Index Scan = ordered table scan) followed by a hash match join. This kind of operation can be greatly affected by hardware performance/contention, as the hash match executes faster with more ram.

Comparing the plans might shed some light on it, but if it runs adequately now... :)

|||The optimiser will expand out queries like the first one. Just one of those annoying things about it. You could improve the speed by putting changing it to "select top (999999) distinct col1, col2, col3 from myView", because that will force the server to materialise the table first. Any number will do, so long as it's larger than the number of rows you expect to get out (otherwise you'll lose rows).

Which effectively is the same as populating a temporary table. :)

Rob|||

First of all, thank you all for your replies.
Rob, will you please tell me, what did you mean by "materialise table".
Did you mean that database would move view from memory to hard drive?
And, if that is true, would db engine make that table within tempdb space (as temp tables are) or in original database space?

|||When I say 'materialise', I just mean that the engine will create the table in memory and then join the other tables to it. It won't move it from memory to the hard drive - it will stay in RAM.

Rob|||

ok.Thanks. Then it works as I expected.

|||Ah, great. Glad to help. Can you mark it as an answer please? I know MS are keen for this to happen on all threads.|||With pleasure :) Done|||:) Cheers