Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2015
    Posts
    3

    Answered: Pull all records even if other table finds no related record in the condition.

    Hi, I am a little stuck and would really appreciate some help. I have 2 tables: Applic and Tempad once holds the current details and the other any temporary addresses

    Attributes
    Applic: - Application_no, Address1, Address2, etc.
    Temppad: Applic_no, tempadd1, tempadd2, etc.

    A record will only be added into tempadd if a temporary address has been entered. So lets say I have 5 applications and only 2 of them have a temporary address. The tempadd table would only have records for applic no's 100,102.
    Code:
    Applic No	Address1	        tempadd1
    100		1 test st	        1 temp st
    101		2 test st		
    102		4 test st		4 temp st
    103		5 test st		
    104		6 test st		
    105		7 test st

    Code:
    SELECT
      applic.application_no,
      pub.tempad.addr1
    FROM
      applic,
      tempad
    WHERE
    ( tempad.application_no=applic.applic_no  )
    This, as expected, will only return applications 100 and 102 as they are the only records that have a corresponding entry in Temppad. But Id like to return all records even if there is no corresponding record in tempadd. A little like the layout in the table above. Can it be done?

    Cheers,
    Jp

  2. Best Answer
    Posted by Pat Phelan

    "Yes, what you've described is an outer join. Look up outer join in the MySQL documentation to see how to apply it to your problem.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, what you've described is an outer join. Look up outer join in the MySQL documentation to see how to apply it to your problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2015
    Posts
    3

    Great stuff.

    Quote Originally Posted by Pat Phelan View Post
    Yes, what you've described is an outer join. Look up outer join in the MySQL documentation to see how to apply it to your problem.

    -PatP
    Thanks Pat. The OUTER JOIN didn't quite work, but I think that it more to do with the constraints of the reporting software we use (Business Objects). However, when looking up Outer Joins I of course stumbled across the others and found the LEFT JOIN, which seems to work.

    This opens up a whole new world of reporting

    Cheers for taking the time to reply.
    JP

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    OUTER JOIN doesn't work by itself, it needs a qualifier like LEFT as you discovered. You're on the right track and a happy camper (with all kinds of new ideas rumbling around in your head I'll bet), so all is good!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Oct 2015
    Posts
    3
    Ahh that makes sense. I'll definitely have a good play with all these joins.
    Cheers.

Posting Permissions

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