Joining question on SQL

Hi... I got a problem...
I want to join two tables with these columns

AQ004088-1
AQ004089-2
MD-001-1
MD72993-2
AQS99201-1
MS88273-2

and the table
AQ: Aquarius Music
AQS: Aquarius/Pacific
MD: Musica Delta
MD-: PolyGram
Can someone help me how to join these tables properly? e.g AQ not overlap with AQS992201-1. so the output will be

AQ004088-1 = Aquarius Music
AQ004089-2 = Aquarius Music
MD-001-1 = PolyGram
MD72993-2 = Musica Delta
AQS99201-1 = Aquarius/Pacific
MS88273-2 = (unknown)

if i use
tb1 left outer join tb2 on tb2.id = left(tb1, len(tb2))

the result will have double data on more than 1 occurence one, like AQS and MD-, i got these results instead

AQ004088-1 = Aquarius Music
AQ004089-2 = Aquarius Music
MD-001-1 = PolyGram
MD-001-1 = Musica Delta
MD72993-2 = Musica Delta
AQS99201-1 = Aquarius Music
AQS99201-1 = Aquarius/Pacific
MS88273-2 = (unknown)

Can someone help me to correct this behaviour? thank you.the problem appears to be that it's a variable length substring on the left side of the whatchamacallit (serial number?), i.e. the difference between MD and MD- seems to be important

try using the PATINDEX function to find the position of the first numeric

tb1 left outer join tb2
on tb2.id = left(tb1.id, patindex('[0-9]',tb1.id)-1)

rudy|||Oh... thank you.. another question if you don't mind...
I want to create the pivot table programmatically, so in this case, after i got the

Select tb1.id, tb2.name, tb1.qty from
tb1 left outer join on tb2 tb2.id = left(tb1.id, patindex('[0-9]',tb1.id)-1)

I want to put all the tb2.name into columns like
select tb1,id, sum(case when tb2.name = 'Aquarius' then qty else 0 end) as 'Aquarius', sum(case when tb2.name = 'Musica Delta' then qty else 0 end), ... (until all columns listed)

How can I add those case columns programmatically, so that it will bring the name like:

sum(case when tb2.name = 'ID Name' then qty else 0 end) as [ID Name]

Where [ID Name] list is retrieved from:
Select distinct tb2.Name FROM
tb1 left outer join on tb2 tb2.id = left(tb1.id, patindex('[0-9]',tb1.id)-1)

Thank you.

BTW, my name is also Rudy .. :) hehe|||programmatically? no idea

a pivot or crosstab display is best done outside of sql

rudy