[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

MSACCESS join with a like clause added

Hi,

Is it possible to perform some kind of join of one table with another, where the second table is just a list of names. I want everything extracted from the first table, where the linked column in the first table contains the value in the second table. i.e. a LIKE *name* clause. So the join doesn't have to be exact, just 'contain' the value in the table which has a list of names.

So, if the first table containted the name 'John Fox''and the second table has the value 'Fox' then the first tables row will be returned since Fox is found within John Fox. Does that make sense?

(I just want to do this in ACCESS, although i'd like to know the sql syntax also)
0
AidenA
Asked:
AidenA
  • 8
  • 7
  • 3
3 Solutions
 
js-profiCommented:
select distinct t1.* from tab1 t1, tab1 t2
where t1.name like '%'+t2.name+'%';

i hope the + will be accepted in access sql.
0
 
AidenAAuthor Commented:
hi thanks,

i'm not quite sure what i'm looking at there. t1 and t2 must be table 1 and table 2. but then i don't know what tab1 is?
0
 
js-profiCommented:
it is a typo should be tab2
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AidenAAuthor Commented:
humm... ok so if t1 is table 1 and t2 is table 2, then what is tab2 suppose to be?
0
 
Patrick MatthewsCommented:
SELECT t1.*
FROM [NameOfTable1Here] t1 INNER JOIN
      [NameOfTable2Here] t2 ON "*" & t1.[Name] & "*" Like t2.[Name]


Replace the table/column names above as needed.

Access uses & for concatenation and * as the "all" wild card.
0
 
js-profiCommented:
i used tab1 and tab2 as you didn't give names. are you kidding?
0
 
js-profiCommented:
thanks matthew, i remember the * and the &. single escape char is ?, yes?
0
 
Patrick MatthewsCommented:
js-profi said:
>>and the &. single escape char is ?, yes?

Either that, or underscore.  Can't remember which :)
0
 
AidenAAuthor Commented:
am i kidding? are you using t1 and tab1 to both mean table 1?  how am i not supposed to be confused? well anyway, you need to tell me what t1 is then if tab 1 is suppose to be table 1??

like select distinct t1.* from tab1 t1, tab1 t2

that doesn't look like proper sql to me? normally you would have

select distinct table1.* from Table1 where ...

but i'm just getting confused now because you are using tab1 t1 which makes no sense to me, but maybe i'm just not used to sql enough

ok thanks guys, but i'll have to check tomorrow, best of luck!
0
 
Patrick MatthewsCommented:
AidenA,

Please don't get frustrated :)

The t1 and t2 in my example are table aliases; they are in fact a SQL technique used to simplify queries.

In my example, it should be obvious where you would put your actual table names.

Patrick
0
 
AidenAAuthor Commented:
no, no problem, actually i responded to this last week but for some reason it didn't submit. anyway, i'm not sure how to implement the query you have above, something's wrong with it (or my interpretation probably). I have the query below. There's something wrong with the * and & placements. Just looking at it actually i can tell it's not going to work, in fact, i don't think i've ever seen the & character used in a query like this... are you sure it's correct?
"SELECT * INTO `Extract By Router` FROM(`" & strSourceTable & "`) INNER JOIN `Routers` ON * & `" & strSourceTable & "`.`Service_Identifier` & * LIKE `Routers`.`Routers`"

Open in new window

0
 
js-profiCommented:
if you see a sql statement with from clause, the next word _always_ is a table name or  a view name. If there is another specifier following the table or view name it _always_ is alias name which could be used to make fieldnames inambiguous. i didn't want to confuse you but that is common sql technique when defining joint selects.
0
 
AidenAAuthor Commented:
yeah sorry i'm still confused about that :-/

but maybe i'll learn something from it anyway

so you said: select distinct t1.* from tab1 t1, tab1 t2

tab1 was fairly obviously just the name for any table. So if you use aliases then you have to use the 'AS' identifier. So, the only thing i can guess is that you are writing it, not as real sql, but as an sql technique (as you say) where it's almost kind of like saying 'Dim t as table', so that you are just writing in a more mathematical kind of way.

*If* that is the case then you can see how i'd get confused if i was expecting proper sql... is that the case?

Thanks, Aiden

0
 
AidenAAuthor Commented:
ok i'll just have to leave it the way i have it at the moment as i don't really have anymore time to spend on this one so i'll just close the question.

thanks, Aiden
0
 
js-profiCommented:
the 'as' can be used optional. i don't know a dbms which wouldn't allow to omit the 'as',

using one or two letter aliases in a sql statement is normal cause the sql statement get much more readable by this.
0
 
js-profiCommented:
there is a solution. please post your statement not working and the error message.
it could be that the solution is not perfect. if searching for 'and' in a sentence like 'the band played the whole night) you'll get a hit.
0
 
AidenAAuthor Commented:
Hey, problem is post 01/11/10 02:08 PM, ID: 26283231 above. Not sure how to implement the query or exactly what it was that i should have written
0
 
js-profiCommented:
matthewspatrick posted a good sample in no 26192810. first try to get the statement run in sql window of access not using VB strings for table names. then replace tablenames by variables using

    " & strTable & "

though i dont see much benefit doing so. how many different combinations of tables you have where the query should run? if only one use the table names and not the string variables.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 8
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now