wildcards in joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

wildcards in joins

Author

Topic

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-03-05 : 04:45:32

so i have 2 tables - tblPages and tblPages2

tblPages contains a list of every variation of url on my site

so it could be as follows

tblPages
page:
http://www.mysite.com/index.aspx?id=1
http://www.mysite.com/index.aspx?id=2
http://www.mysite.com/index.aspx?id=3
http://www.mysite.com/index.aspx?id=4
http://www.mysite.com/index2.aspx?id=1
http://www.mysite.com/index2.aspx?id=2
etc

tblPages2
page: / pageName
http://www.mysite.com/index.aspx / home page

could i do a join based on the page column from both tables where page in tblPages = page with a wildcard attached after it (to cover all available query strings) in tblPages2?

asgast
Posting Yak Master

149 Posts

Posted - 2009-03-05 : 05:07:14

yes you can, but it will slow everything down

pootle_flump

1064 Posts

Posted - 2009-03-05 : 05:55:50

Slow things down from what? The other alternative is SUBSTRING() which will be much slower. Indexes can still be used for this type of wildcard search.

It looks like your logic is the wrong way round. Is tblPages(page) a substring of tblPages2(page)?
if so:

.......
tblPages2
INNER JOIN
tblPages
ON tblPages.Page LIKE tblPages2.Page + '%'

HTH

asgast
Posting Yak Master

149 Posts

Posted - 2009-03-05 : 06:07:58

other alternate is to make an additional column, where you put the part you'll be joining by

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-03-05 : 06:38:44

the page column in tblPages2 is a url without querystrings
the page column in tblPages contains all my url possibilities (meaning all querystrings applicable)

so at the select stage i'm trying to chop off anything after the ? from the querystring url in page column values from tblPages
can you use a split function at a certain character (remember i wont know the index of the character)

pootle_flump

1064 Posts

Posted - 2009-03-05 : 07:33:47

If you have indexes, you will lose the use of indexes. I would absolutely not do that. You really have two choices:
Replace your column with two columns. Something like rootURL and URLparameters. Simple inner join on tblPages2.Page and tblPages.rootURL. So this requires a schema change. It also allows relational integrity since you can set up a foreign key between the tables.
Otherwise stick with using like. Please don't go chopping up the strings at run time - that way lies dragons.

pootle_flump

1064 Posts

Posted - 2009-03-05 : 07:34:53

Hang on - "at the select stage". Are you still talking about joins, or what is displayed?

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-03-05 : 11:12:37

thanks pootle, your first post fixed it using like. i was putting = instead of like (yes i am a pleb)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources