basic query join tables



  • i have a master table mail:

    mail
    mail1
    mail2

    and several tables of different systems like this:

    user xxx mail
    First mail1
    Second mail2

    only field in common is mail...

    i want a single table that contains the different user columns, and BLANK if user is not found...

    user xxx user yyyy user zzzz mail
    First fiirst y first z mail1
    Second second y BLANK mail2

    seems easy but im very RUSTY.

    regards



  • If I understand your question correctly, you need to do outer joins to the user tables. Since you didn't provide create statement and the the insert for the sample data, I can only guess it should be something like:

    select xxx.xxx userxxx, yyyy.yyyy useryyyy, zzzz.zzzz userzzzz, mail.mail
    from mail left outer join xxx
           on mail.mail = xxx.mail
              left outer join yyyy
           on mail.mail = yyyy.mail
              left outer join zzzz
           on mail.mail = zzzz.mail;
    

    Note that SQL will return NULL for unmatched value in outer join. You can use COALESCE function if you want the literal "BLANK", like COALESCE(xxx.xxx, 'BLANK')


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2