If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
wildcard symbols around a field in a join?
Hi,
This is my SQL QUERY: SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME; The issue is that Table2 has NAME values that should join with NAME values in Table 1, but have extra characters. Example: Table1 ----------- NAME ---- RITSON KING STEVENSON Table2 ----------- NAME ---- RITSON (NORTH LIMIT) KING (WEST LIMIT) STEVENSON (SOUTH LIMIT) I'd like the values to join, even though they don't exactly match. Is it possible to have a wildcard symbols around a field in a join? This is what it would look like in my head: (look for the *'s) SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *; -Ben |
#2
|
|||
|
|||
wildcard symbols around a field in a join?
Try this if the separator is always the ( :
SELECT Table1.[NAME], Table2.[NAME] FROM Table1 LEFT JOIN Table2 ON Table1.[NAME] = Trim(Left(Table2.[NAME], Instr(Table2.[NAME],"(")-1)) -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Ben8765" wrote: Hi, This is my SQL QUERY: SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME; The issue is that Table2 has NAME values that should join with NAME values in Table 1, but have extra characters. Example: Table1 ----------- NAME ---- RITSON KING STEVENSON Table2 ----------- NAME ---- RITSON (NORTH LIMIT) KING (WEST LIMIT) STEVENSON (SOUTH LIMIT) I'd like the values to join, even though they don't exactly match. Is it possible to have a wildcard symbols around a field in a join? This is what it would look like in my head: (look for the *'s) SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *; -Ben |
#3
|
|||
|
|||
wildcard symbols around a field in a join?
Ben8765 wrote:
Hi, This is my SQL QUERY: SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME; I'd like the values to join, even though they don't exactly match. Is it possible to have a wildcard symbols around a field in a join? This is what it would look like in my head: (look for the *'s) SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *; Just delimit the wildcards and change = to LIKE and go with it. ON Table1.NAME LIKE '*' & Table2.NAME & '*'; You won't be able to view this query in Design View, but don't let that stop you. What should be concerning you is performance, since the query engine will not be able to use an index to speed this up. -- HTH, Bob Barrows |
#4
|
|||
|
|||
wildcard symbols around a field in a join?
You can use any operator (even user defined function returning Boolean) in a
join: SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON TRIM(Table1.NAME) = TRIM(Table2.NAME); It is slower, though, than if you don't have to, since now, the index cannot be used. Vanderghast, Access MVP "Ben8765" wrote in message ... Hi, This is my SQL QUERY: SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME; The issue is that Table2 has NAME values that should join with NAME values in Table 1, but have extra characters. Example: Table1 ----------- NAME ---- RITSON KING STEVENSON Table2 ----------- NAME ---- RITSON (NORTH LIMIT) KING (WEST LIMIT) STEVENSON (SOUTH LIMIT) I'd like the values to join, even though they don't exactly match. Is it possible to have a wildcard symbols around a field in a join? This is what it would look like in my head: (look for the *'s) SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *; -Ben |
#5
|
|||
|
|||
wildcard symbols around a field in a join?
SELECT Table1.NAME, Table2.NAME
FROM Table2 RIGHT JOIN Table1 ON Table2.NAME LIKE Table1.NAME & "*"; John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Ben8765 wrote: Hi, This is my SQL QUERY: SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME; The issue is that Table2 has NAME values that should join with NAME values in Table 1, but have extra characters. Example: Table1 ----------- NAME ---- RITSON KING STEVENSON Table2 ----------- NAME ---- RITSON (NORTH LIMIT) KING (WEST LIMIT) STEVENSON (SOUTH LIMIT) I'd like the values to join, even though they don't exactly match. Is it possible to have a wildcard symbols around a field in a join? This is what it would look like in my head: (look for the *'s) SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *; -Ben |
#6
|
|||
|
|||
wildcard symbols around a field in a join?
On Tue, 23 Feb 2010 13:16:01 -0800, Ben8765
wrote: Hi, This is my SQL QUERY: SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME; The issue is that Table2 has NAME values that should join with NAME values in Table 1, but have extra characters. Example: Table1 ----------- NAME ---- RITSON KING STEVENSON Table2 ----------- NAME ---- RITSON (NORTH LIMIT) KING (WEST LIMIT) STEVENSON (SOUTH LIMIT) I'd like the values to join, even though they don't exactly match. Is it possible to have a wildcard symbols around a field in a join? This is what it would look like in my head: (look for the *'s) SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *; -Ben You're close, but you need the LIKE operator (which recognizes wildcards), not the = operator (which requires an exact match). You also had the wildcards on the wrong table - they need to be on Table1.NAME to match the extra characters in Table2: SELECT Table1.[NAME], Table2.[NAME] FROM Table1 LEFT JOIN Table2 ON Table2.[NAME] LIKE "*" & Table1.[NAME] & "*"; Your example only shows cases where the field values match on the left and differ on the right - to prevent the query from being bog-slow and probably not updateable I'd use only the second asterisk, unless you also have values in Table1 with spinach to the left as well as to the right. Note that I bracketed NAME - it's a reserved word and should generally NOT be used as a fieldname; if you do so it's much safer to consistantly bracket it. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
wildcard symbols around a field in a join?
Thanks everyone for all your replies! This has been a huge help.
-Ben "John W. Vinson" wrote: On Tue, 23 Feb 2010 13:16:01 -0800, Ben8765 wrote: Hi, This is my SQL QUERY: SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME; The issue is that Table2 has NAME values that should join with NAME values in Table 1, but have extra characters. Example: Table1 ----------- NAME ---- RITSON KING STEVENSON Table2 ----------- NAME ---- RITSON (NORTH LIMIT) KING (WEST LIMIT) STEVENSON (SOUTH LIMIT) I'd like the values to join, even though they don't exactly match. Is it possible to have a wildcard symbols around a field in a join? This is what it would look like in my head: (look for the *'s) SELECT Table1.NAME, Table2.NAME FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *; -Ben You're close, but you need the LIKE operator (which recognizes wildcards), not the = operator (which requires an exact match). You also had the wildcards on the wrong table - they need to be on Table1.NAME to match the extra characters in Table2: SELECT Table1.[NAME], Table2.[NAME] FROM Table1 LEFT JOIN Table2 ON Table2.[NAME] LIKE "*" & Table1.[NAME] & "*"; Your example only shows cases where the field values match on the left and differ on the right - to prevent the query from being bog-slow and probably not updateable I'd use only the second asterisk, unless you also have values in Table1 with spinach to the left as well as to the right. Note that I bracketed NAME - it's a reserved word and should generally NOT be used as a fieldname; if you do so it's much safer to consistantly bracket it. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|