Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Exclamation Unanswered: SQL Data Manipulation Question -- Crazy man!



    Below is a sample recordset from a table in my MySQL 4.1 DB. You can see 5 rows of data below. Each row has a langID associated with it (1=Eng, 2=Japanese & 3=Korean). If my primary (selected) language is Japanese(2), then I need to grab only 3 of the following records:

    - 73(langID:1) - because there is no Japanese version.
    - 69(langID:2) - the Japanese Version
    - 82(langID:3) - because there is no Japanese version

    Note: if there is a (langID=1) and (langID=3) value in the results above AND NO (langID=2) equivalent, then I need to grab the English version. (how's that for a twist?)

    Is this possible with SQL or do I need to come up with a Cold Fusion solution?

    Thanks for the brain-cycles...

    Code:
    CompanyPID     company           langID
    73             CAPITOL RECORDS     1
    69             DRT RECORDS         1
    69             DRT の記録             2
    69             DRT 부용공             3
    82             BMI 쯔이               3

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please let me know if this works --
    Code:
    select L2.CompanyPID
         , L2.company
      from yourtable as L2
     where L2.langID = 2
    union all
    select CompanyPID
         , company
      from yourtable as L1
    left outer
      join yourtable as L2
        on L1.CompanyPID 
         = L2.CompanyPID 
       and L2.langID = 2     
     where L1.langID = 1
       and L2.CompanyPID is null 
    union all 
    select CompanyPID
         , company
      from yourtable as L3
    left outer
      join yourtable as L2
        on L3.CompanyPID 
         = L2.CompanyPID 
       and L2.langID = 2     
    left outer
      join yourtable as L1
        on L3.CompanyPID 
         = L1.CompanyPID 
       and L1.langID = 1
     where L3.langID = 3
       and L2.CompanyPID is null 
       and L1.CompanyPID is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    p.s. please don't cross-post

    i've deleted the duplicate post in the SQL forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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