Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Unanswered: 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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  3. #3
    Join Date
    Sep 2002
    Posts
    30
    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
    Last edited by prd00; 10-29-02 at 09:04.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    programmatically? no idea

    a pivot or crosstab display is best done outside of sql


    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •