Joining Tables

20 Jan 2006 8:21 AM

John

help please

I have two tables I want to join, Table 1 (T1) nd Table 2 (t2). Table 1 has
several fields A1,B1,C1......... etc and Table 2 has several Fields A2,B2,B3
etc

I want to join A1 to A2, which is simple enough, but then I want to join B1
to B2 based on the field value in B1 being in the field B2

eg Find 'Dog' (B1) in the 'Dog and the Cat' (B2) or find 'Hat' (B1) in 'Top
Hat' (B2)

Can any one advise how I can achieve this ?

Thanks John

20 Jan 2006 8:34 AM

Uri Dimant

John

SELECT <> FROM Table1 JOIN Table2
ON Table1.A1=Table2.A2 AND Table1.B1=Table2.B2

Does it help you?

Show quote

"John" <topgu***@hotmail.com> wrote in message
news:43d09d82$0$23296$db0fefd9@news.zen.co.uk...
> help please
>
> I have two tables I want to join, Table 1 (T1) nd Table 2 (t2). Table 1
> has several fields A1,B1,C1......... etc and Table 2 has several Fields
> A2,B2,B3 etc
>
> I want to join A1 to A2, which is simple enough, but then I want to join
> B1 to B2 based on the field value in B1 being in the field B2
>
> eg Find 'Dog' (B1) in the 'Dog and the Cat' (B2) or find 'Hat' (B1) in
> 'Top Hat' (B2)
>
> Can any one advise how I can achieve this ?
>
> Thanks John
>

20 Jan 2006 8:52 AM

Jens

I think he wants to check for approximality not equality. B1 attribute
has to be appear "somewhere" in the B2 attribute. My only thought for
this as a setbased solution is to use CONTAINS.

-Jens Suessmeyer.

20 Jan 2006 9:16 AM

Uri Dimant

You are probably right

SELECT <> FROM Table1 JOIN Table2
ON Table1.A1 LIKE '%' + Table2.A2 + '%'
AND Table1.B1 LIKE '%' + Table2.B2 + '%'

Show quote

"Jens" <J***@sqlserver2005.de> wrote in message
news:1137747168.036246.222290@o13g2000cwo.googlegroups.com...
>I think he wants to check for approximality not equality. B1 attribute
> has to be appear "somewhere" in the B2 attribute. My only thought for
> this as a setbased solution is to use CONTAINS.
>
> -Jens Suessmeyer.
>

20 Jan 2006 9:20 AM

Jens

Assuming that the original poster wants equality on the A columns, this
should be

SELECT <> FROM Table1 JOIN Table2
ON Table1.A1 = Table2.A2
AND Table1.B1 LIKE '%' + Table2.B2 + '%'

-Jens Suessmeyer

20 Jan 2006 10:50 AM

John

Thanks for you help both of you, worked a treat

John

Show quote

"Jens" <J***@sqlserver2005.de> wrote in message
news:1137748855.153688.236890@g49g2000cwa.googlegroups.com...
> Assuming that the original poster wants equality on the A columns, this
> should be
>
> SELECT <> FROM Table1 JOIN Table2
> ON Table1.A1 = Table2.A2
> AND Table1.B1 LIKE '%' + Table2.B2 + '%'
>
> -Jens Suessmeyer
>