LIKE % % with inner join

16 Jul 2006 11:07 AM

Bonzol

hey there,

This is for a search function /What I want to do is, return a datatable
with all the ID's that have the required values. This is easy enough
when everything I want to enter is in the same table. But I need to
check against some entries that are not int he same tables. At the
moment I have this.

SQL = "SELECT Job FROM JOBS WHERE (job LIKE '%" + jName + "%') AND
(StrtDate LIKE '%" + datestarted + "%') "

This works fine, however, I need to add a part that will do this with
an inner join.
So,, I want someone to enter the job name , start date AND street name
(which is in another tables) and it will go to the 'Adresses' table and
return all the ID's in the jobs table that have that street name linked
to them in the adreses table. my SQL for the select command of Jobs and
adresses is

SQL = "SELECT Addresses." + returnColumn + " FROM Addresses INNER JOIN
Jobs ON Addresses.ID=Jobs.SiteAdID WHERE (((Jobs." + checkColumn + ")="
+ checkValue + "))"

So how do I make the 'LIKE' SQL string work with the inner join?

Thanx in advance

7 Jul 2006 4:15 PM

Cor Ligthert [MVP]

Bonzol,

This is an often asked question in the ADONET newsgroup, there is however
one LIKE if you need more diferent parameters, than you have to create a
string with a WHERE AND clause. The SQL language is not very strong in that.

Cor

Show quoteHide quote

"Bonzol" <Bon***@hotmail.com> schreef in bericht
news:1153048032.008359.289240@b28g2000cwb.googlegroups.com...
> hey there,
>
> This is for a search function /What I want to do is, return a datatable
> with all the ID's that have the required values. This is easy enough
> when everything I want to enter is in the same table. But I need to
> check against some entries that are not int he same tables. At the
> moment I have this.
>
> SQL = "SELECT Job FROM JOBS WHERE (job LIKE '%" + jName + "%') AND
> (StrtDate LIKE '%" + datestarted + "%') "
>
> This works fine, however, I need to add a part that will do this with
> an inner join.
> So,, I want someone to enter the job name , start date AND street name
> (which is in another tables) and it will go to the 'Adresses' table and
> return all the ID's in the jobs table that have that street name linked
> to them in the adreses table. my SQL for the select command of Jobs and
> adresses is
>
> SQL = "SELECT Addresses." + returnColumn + " FROM Addresses INNER JOIN
> Jobs ON Addresses.ID=Jobs.SiteAdID WHERE (((Jobs." + checkColumn + ")="
> + checkValue + "))"
>
> So how do I make the 'LIKE' SQL string work with the inner join?
>
> Thanx in advance
>