Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    10

    Question Unanswered: Printing output even though no results found

    Hi, I've written a report to search an Inventory table using a list of Locations.
    The report will output a list of locations with Inventory quantities.
    But the problem I have is that if one of the Locations doesn't have any Inventory, then this Location isn't printed on the report.
    I know that is what's meant to happen since the SELECT function will only display rows where a match is found, but is there anyway I can display the search criteria (i.e. the locations) even if there is no corresponding value in the Inventory table?

    Thanks.
    Jaz

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What reporting tool are you using?

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one word: LEFT OUTER JOIN

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2004
    Posts
    10
    I'm not using a reporting tool, just SQL written report.

    r937: what do you mean by left outer join? sorry for my ignorance.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    consider

    Pets
    1 dog
    2 cat
    3 bird
    4 ferret

    People
    35 curly
    38 larry
    39 moe

    PeoplePets
    35 2
    35 3
    39 1


    list all people with pets (INNER join) --

    curly cat
    curly bird
    moe dog

    list all people, and their pets if any (LEFT OUTER join) --

    curly cat
    curly bird
    larry NULL
    moe dog

    list all people and a count of their pets (LEFT OUTER join) --

    curly 2
    larry 0
    moe 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2004
    Posts
    10

    Talking Thanks!

    Hi r937, thanks for your help. I've managed to get it working now.

    Much appreciated !!

Posting Permissions

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