0

I have a 2 tables

CREATE TABLE testmain
(dummy_Id numeric(10,0) null,
 memberNo varchar(100)  null)

CREATE TABLE testchild
(dummy_Id numeric(10,0) null,
 xummy_Id numeric(10,0) null)

where xummyId is the dependent meber dummyId

The testmain table has data as of these:

SELECT * FROM testmain

1001 82411/1
1002 82411/2
1003 85545/1
1004 82411/3
1005 85545/2

The left part of memberNo is the code and the last part is the dependent member.

So in the above example 82411/1 is the master and 82411/2,82411/3 are its child members

I need to parse the testmain table and for each entry of type code/1 and insert in the testchild the dummyIds of the child members

SELECT * FROM testchild

1001 1002
1001 1004 
1003 1005 

Thanx in advance

1

patindex can do want you expect:

select a.dummy_Id, b.dummy_Id
from testmain a
join testmain b on left(a.memberNo,patindex('%/%', a.memberNo)-1) = left(b.memberNo,patindex('%/%', b.memberNo)-1)
                    and b.memberNo not like '%/1'
                    and a.dummy_Id <> b.dummy_Id
and a.memberNo like '%/1'
3
  • I get an "Invalid length parameter passed to the LEFT or SUBSTRING function" message :(
    – PanosPlat
    Jan 9 '17 at 13:28
  • I removed the -1 and it seems to work!
    – PanosPlat
    Jan 9 '17 at 13:31
  • @Panos : the query I made was under the assumption that memberNo was always xxxxxx/y. If it doesn't contain any '/', if it will fail
    – irimias
    Jan 9 '17 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.