Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    46

    Unhappy Unanswered: Self join problem

    Hi helper
    Here is a query I created all from one table:

    SELECT distinct ML1.Caption AS English,ML2.Caption AS Spanish FROM MULTI_LANGUAGE_CAPTION AS ML1,MULTI_LANGUAGE_CAPTION AS ML2 where ML1.CaptionID = ML2.CaptionID and ML1.LanguageID = 1 and ML2.LanguageID=2

    if MULTI_LANGUAGE_CAPTION is:
    LanguageID || CaptionID || Caption
    1 || 1 ||English:Product1
    1 || 1 ||English:Product2
    1 || 1 ||English:Product3
    2 || 1 ||Spanish:Product1
    2 || 1 ||Spanish:Product2
    2 || 1 ||Spanish:Product3

    the result i will see on a grid is:

    English || Spanish

    English:Product1 || Spanish:Product1
    English:Product2 || Spanish:Product2
    English:Product3 || Spanish:Product3


    My problem is, if my table is :
    LanguageID || CaptionID || Caption
    1 || 1 || English:Product1
    1 || 1 || English:Product2
    1 || 1 || English:Product3
    2 || 1 || Spanish:Product1

    How can the result be:

    English || Spanish

    English:Product1 || Spanish:Product1
    English:Product2 || null
    English:Product3 || null


    please advise

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    You are getting exactly what you asked for. What did you want the second result to be? If you only wanted the first line without second and third that have the nulls, then you will have to make sure you specify where is not null for the captions.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ML1.Caption AS English
         , ML2.Caption AS Spanish 
      FROM MULTI_LANGUAGE_CAPTION AS ML1
    LEFT OUTER
      JOIN MULTI_LANGUAGE_CAPTION AS ML2 
        ON ML1.CaptionID = ML2.CaptionID 
       AND ML2.LanguageID = 2
     WHERE ML1.LanguageID = 1
    rudy
    http://r937.com/

  4. #4
    Join Date
    Aug 2002
    Posts
    46
    Great,it works great
    thanks alot

Posting Permissions

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