Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 29th, 2004, 01:23 PM
Registered User
 
Join Date: Sep 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Relating 2 Tables be search for single field value

Can anyone help me with this? I'm not sure if I have to use VBA or a simple SQL statement.

I am currently designing a database and I have come across a bump. I have two tables that have similar data but do not have exact values being similar in each table. I am trying to relate the tables through a query that uses the values of table1.Name and searches for each value in table2.Name.

Eventually I will relate the results to a second similar search (using the address fields) to cross reference the results.

The database is designed like this.

Table1
ID (unique to table, not in common with table2.ID
Name (one word)
Address (Street address)

Table2
ID (unique to table, not in common with table1.id
Name (entire extended name)
Address (Full address)


 
Old September 29th, 2004, 02:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Just inner join the values

select * from Table1 inner join Table2 on Table1.Name = Table2.Name

Is that what you need?
 
Old September 29th, 2004, 02:57 PM
Registered User
 
Join Date: Sep 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

that would be the easiest if table1.name and table2.name were identical in values. However, table1.name contains one word version of table2.name (for instance Microsoft in table1.name and Microsoft Corporation in table2.name). I need a way to search table2.name for each value in table1.name field.

 
Old September 30th, 2004, 12:33 AM
Friend of Wrox
 
Join Date: Sep 2004
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Anantsharma Send a message via Yahoo to Anantsharma
Default

Hi,

If I got ur question correctly, U r looking for matching words in table1 and table2 on a common colum.

U can use same Inner join with LIKE operator. lIKE OPERATOR CAN BE USED IN Inner Join colum itself or using a Where condition

SELECT TABLE1.NAME,TABLE2.NAME FROM TABLE1 INNER JOIN TABLE2
ON TABLE1.ID = TABLE2.ID
WHERE TABLE2.NAME LIKE '%'+ TABLE1.NAME + '%'
ORDER BY TABLE1.NAME

SELECT TABLE1.NAME,TABLE2.NAME FROM TABLE1 INNER JOIN TABLE2
ON TABLE1.ID = TABLE2.ID
WHERE TABLE2.ADDRESS LIKE '%'+ TABLE1.ADDRESS + '%'
ORDER BY TABLE1.ADDRESS

U can use '%' both sides to compare. There are some other ways e.g PatIndex, CharIndex etc.

I feel u should use the '%' both sides while comparing, It match the pattern.

Hope this helps.

B. Anant
 
Old October 4th, 2004, 09:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

If using SQL Server, you may be able to use full-text indexing to compare versions of a word using FORMSOF(INFLECTIONAL())

Brian





Similar Threads
Thread Thread Starter Forum Replies Last Post
single submit to related tables forumuser BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 10 December 2nd, 2007 10:29 AM
edit single field stealthdevil Access VBA 3 November 16th, 2006 01:19 PM
Need Help on Retriving the value of single field rhythm ASP.NET 1.0 and 1.1 Basics 2 July 6th, 2005 12:57 AM
Storing complete table rows in a single field KenNeedham Oracle 2 January 22nd, 2004 01:45 PM
Showing single and double quote in text field. ggriffit SQL Server ASP 4 January 5th, 2004 11:19 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.