locked
query null values in related tables RRS feed

  • Question

  • I've got two tables that are related via one to many.  How can I add criteria in a query with these two tables to allow the one table to show it's records even if the many table doesn't have records.  I want to show all the records in both queries but some records of the one table do not have any records in the many table. 


    TAK
    Thursday, December 23, 2010 4:45 PM

Answers

  • If you can get to the join properties your properties should be

    between company and equipment

         all in company and only those that match in equipment

    between equipment and attachment

        all in equipment and only those that match in attachment

     

    This will basically give you all companies and if there happens to be records in equipment, it will give you that data as well and if there is data from company to equipment and there happens to be data in attachment, it will give you that data as well

    Why don't you start with company and equipment and when you're sure you're getting all companies, then add attachment to the query.

     

    or start a new query design and copy paste

    SELECT companytbl.*, equipmenttbl.*, swhwattachmenttbl.*
    FROM (companytbl LEFT JOIN equipmenttbl ON companytbl.numCompanyNumber = equipmenttbl.numCompanyNumber ) LEFT JOIN swhwattachmenttbl ON equipmenttbl.autonumberlink = swhwattachmenttbl.autonumberlink ;

    into the sql view and see if that gets you everything you want, if it does just switch to design view and add the fields you want to the columns.


    "The secret of getting ahead is getting started. The secret of getting started is breaking your complex, overwhelming tasks into small, manageable tasks, and then starting on the first one" - Mark Twain

    "Twenty years from now you will be more disappointed by the things you didn't do than by the ones you did. So throw off the bowlines. Sail away from safe harbor. Catch the trade winds in your sail. Explore. Dream. Discover." - Mark Twain

    Please mark/unmark posts if they answered/unanswered your question as these are helpful to other users of this forum. ~ SuzyQ (aka Terry S.)

    • Edited by -suzyQ Thursday, December 23, 2010 9:17 PM use specific fields that original poster had rather than generic
    • Marked as answer by taking Thursday, December 23, 2010 9:49 PM
    Thursday, December 23, 2010 9:08 PM

All replies

  • SELECT TheOneTable.*, TheManyTable.*
    FROM TheManyTable RIGHT JOIN theOneTable ON TheManyTable.ID = TheOneTable.ID;

    Or from the query designer, right click on the link between the two ID fields - select Join properties and then select all records from ______ and only records that match from _______

     


    "The secret of getting ahead is getting started. The secret of getting started is breaking your complex, overwhelming tasks into small, manageable tasks, and then starting on the first one" - Mark Twain

    "Twenty years from now you will be more disappointed by the things you didn't do than by the ones you did. So throw off the bowlines. Sail away from safe harbor. Catch the trade winds in your sail. Explore. Dream. Discover." - Mark Twain

    Please mark/unmark posts if they answered/unanswered your question as these are helpful to other users of this forum. ~ SuzyQ (aka Terry S.)

    • Proposed as answer by -suzyQ Thursday, December 23, 2010 7:09 PM
    Thursday, December 23, 2010 5:14 PM
  • I missed that SuzyQ already stated my answer in her reply and I couldn't figure out how to delete my response so I just removed the contents.

     

    Sorry about that SuzyQ.

    -Mark-


    Mark Annett www.ISPManSys.com
    • Proposed as answer by Mark Annett Thursday, December 23, 2010 6:32 PM
    • Edited by Mark Annett Thursday, December 23, 2010 8:05 PM Already said
    Thursday, December 23, 2010 6:31 PM
  • I did try clicking on the relationship link between the two tables in the query and changing the join options it but neither of the other option is working.  The query I've got has a companytbl with a one to many relationship to the equipmenttbl and then the equipmenttbl has a one to many relationship to the swhwattachmenttbl.  The way the query is working currently is that when I run the query only the records that have data in the swhwattachmenttbl is showing up along with the equipmenttbl data selected and the companytbl data selected.  The only field I'm using in the query for the companytbl is the companynbr field so I think the issue is between the equipmenttbl and swhwattachmenttbl. 

    If the equipmenttbl has a record but the swhwattachmenttbl for that equipmenttbl record does not have any data, that equipmenttbl record isn't showing up in the query.  Sorry for not mentioning the companytbl in my first post.


    TAK
    Thursday, December 23, 2010 8:14 PM
  • that's because you are using inner joins.  Inner joins will only give data when data is present in all tables.  You need to use an outer join (either left or right)  If you can't get to the join properties, then change your design to sql view and change your joins from inner to either left or right depending on which you want to show.

    From companytbl RIGHT JOIN companytbl ON equipmenttbl.ID = companytbl.ID

    and you'll want to do the same for equipmenttbl as your "one" table and swhwattachmenttbl as your "many" table.

    try this use whatever fields you want in the fields list

    SELECT companytbl.*, equipmenttbl.*, swhwattachmenttbl.*
    FROM (companytbl LEFT JOIN equipmenttbl ON companytbl.primaryKey = equipmenttbl.foreignkey) LEFT JOIN swhwattachmenttbl ON equipmenttbl.primarykey = swhwattachmenttbl.foreignkey;

     

    replace primaryKey and foreignKey with the appropriate field names.

     


    "The secret of getting ahead is getting started. The secret of getting started is breaking your complex, overwhelming tasks into small, manageable tasks, and then starting on the first one" - Mark Twain

    "Twenty years from now you will be more disappointed by the things you didn't do than by the ones you did. So throw off the bowlines. Sail away from safe harbor. Catch the trade winds in your sail. Explore. Dream. Discover." - Mark Twain

    Please mark/unmark posts if they answered/unanswered your question as these are helpful to other users of this forum. ~ SuzyQ (aka Terry S.)

    • Proposed as answer by -suzyQ Thursday, December 23, 2010 8:45 PM
    Thursday, December 23, 2010 8:35 PM
  • Below is the sql view of what I got.  Below is only the line where the join options are used.  I've got alot of fields in the query so I left them out.  Let me know if you want all the code.  When I run it it gives me the message "no current record".  I did the LEFT JOIN with the swhwattachment because the option says to include all records in the tblequipmentdatatable and only those records form swhwattachments where the joined fields are equal.  I tried the RIGHT JOIN on it also and it gave me the same records as originally.  I know i'm missing records in the query that don't have records in the swhwattachments table.

    FROM (tblCompany RIGHT JOIN tblequipmentdatatable ON tblCompany.numCompanyNumber = tblequipmentdatatable.numCompanyNumber) LEFT JOIN swhwattachments ON tblequipmentdatatable.autonumberlink = swhwattachments.autonumberlink


    TAK
    Thursday, December 23, 2010 9:01 PM
  • If you can get to the join properties your properties should be

    between company and equipment

         all in company and only those that match in equipment

    between equipment and attachment

        all in equipment and only those that match in attachment

     

    This will basically give you all companies and if there happens to be records in equipment, it will give you that data as well and if there is data from company to equipment and there happens to be data in attachment, it will give you that data as well

    Why don't you start with company and equipment and when you're sure you're getting all companies, then add attachment to the query.

     

    or start a new query design and copy paste

    SELECT companytbl.*, equipmenttbl.*, swhwattachmenttbl.*
    FROM (companytbl LEFT JOIN equipmenttbl ON companytbl.numCompanyNumber = equipmenttbl.numCompanyNumber ) LEFT JOIN swhwattachmenttbl ON equipmenttbl.autonumberlink = swhwattachmenttbl.autonumberlink ;

    into the sql view and see if that gets you everything you want, if it does just switch to design view and add the fields you want to the columns.


    "The secret of getting ahead is getting started. The secret of getting started is breaking your complex, overwhelming tasks into small, manageable tasks, and then starting on the first one" - Mark Twain

    "Twenty years from now you will be more disappointed by the things you didn't do than by the ones you did. So throw off the bowlines. Sail away from safe harbor. Catch the trade winds in your sail. Explore. Dream. Discover." - Mark Twain

    Please mark/unmark posts if they answered/unanswered your question as these are helpful to other users of this forum. ~ SuzyQ (aka Terry S.)

    • Edited by -suzyQ Thursday, December 23, 2010 9:17 PM use specific fields that original poster had rather than generic
    • Marked as answer by taking Thursday, December 23, 2010 9:49 PM
    Thursday, December 23, 2010 9:08 PM
  • Looks like recreating the query helped.  I added the companytbl and then the equipmenttbl to the query and it showed the records I was missing from the other query.  I then added the swhwattachments table to the query and LEFT JOINED it like before and it did retrieved the records from the equipmenttbl I was missing in the other query.  In the swhwattachments table of the new query I gradually added fields and there was one field that was apparently hanging up the query where it would give me the "No current record" but that field was not needed for the query anyway.  Thanks for the help!!
    TAK
    Thursday, December 23, 2010 9:49 PM