Join Issue

my scenario is given below

createtable #product (prodID int, subproductid varchar(20))

createtable #subproduct (subproductid varchar(20),description varchar(40))

Insert #product select 1,'1001/2002'

Insert #product select 1,'3003/4004'

Insert #product select 1,'5005/6006'

insert subproduct select 1001 ,'aaa'

insert subproduct select 2002 ,'bbb'

insert subproduct select 3003 ,'ccc'

insert subproduct select 4004 ,'ddd'

insert subproduct select 5005 ,'eee'

insert subproduct select 6006 ,'fff'

this is how our two tables is related. i know its a bad design . but i can't help it.

my question is how can i join these two table ?

thanks in advance

Leena S

S Leena,

IF you know that the table design is bad, why can't you fix it? Do you need help in understanding why it is so bad?

Apparently, from the way the data is put together, and looking at this query, someone made some boneheaded decisions about how to store data in a database. You can be the 'hero' and correct the 'mistake'.

And then life, with queries such as this, will be so much easier...

|||

Hi,

One of the solutions would be a UDF that splits the text and join with that.

But I too suggest the above remark because the design is against the 'rules' of normalization imho ;-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||You could join using:

SELECT * --Change this
FROM #product JOIN #subproduct
ON '/' + #product.subproductid + '/' like '%/' + #subproduct.subproductid + '/%'

But as suggested already, you should consider changing your structure.

Rob|||

This is really bad design.

Anyhow if you are not authorized to change the design, the following approach may help you..

Code Snippet

create table #product(

prodID int,

subproductid varchar (20)

);

Insert #product select 1,'1001/2002'

Insert #product select 2,'3003/4004'

Insert #product select 3,'5005/6006'

create table #subproduct(

subproductid varchar(20),

description varchar(40)

);

Insert #subproduct select 1001 ,'aaa'

Insert #subproduct select 2002 ,'bbb'

Insert #subproduct select 3003 ,'ccc'

Insert #subproduct select 4004 ,'ddd'

Insert #subproduct select 5005 ,'eee'

Insert #subproduct select 6006 ,'fff'

--Generating Number Tables;

Select Identity(int,1,1) as Number Into #Numbers From #subproduct A Cross Join #subproduct B;

--Getting the results

Select ProdId,description From

(

Select

prodId

,Case When Number <= Len(subproductid) Then Substring('/' + subproductid + '/', Number+1, CharIndex('/',subproductid + '/', Number+1)-Number) End subproductid

from

#product P

Cross Join #Numbers N

Where

Number <= Len(subproductid) AndSUBSTRING('/' + subproductid + '/', number, 1) = '/'

) as Product

Join #subproduct sub on sub.subproductid = Product.subproductid