none
If i am creating a qry with a sub qry must all fields used in both SELECT clauses be listed in first SELECT clause? RRS feed

  • Question

  • I am not sure.  I thought as long as after the "SELECT" all fields were referenced both select clauses would work.

    so if I had something like this

    SELECT * tableFood …….NOT IN (SELECT * tableCategories  something <> "Meat Dish" ….


    to get rows of recipes without meat in them.

    I thought due to the "*" between the "SELECT" and table name that would make all fields available in both select clauses.

    yes I know this is a incomplete SQL statement.   


    Mark J

    Saturday, December 15, 2018 11:02 AM

Answers

  • You'd still use the NOT EXISTS predicate, e.g.

    PARAMETERS Forms!rmVendorContactInfoAndSellsItmes!VendorID LONG;
    SELECT ItemID, Item
    FROM tblItems
    WHERE NOT EXISTS
         (SELECT *
           FROM tblVendorItems
           WHERE ItemID = tblItems.ItemsID
           AND VendorID = Forms!rmVendorContactInfoAndSellsItmes!VendorID
    ORDER BY Item;

    Requery the list box in the form's Current event procedure, and in the subform's AfterUpdate and AfterDelConfirm event procedures.

    Ken Sheridan, Stafford, England

    • Marked as answer by PuzzledByWord Monday, December 17, 2018 10:35 AM
    Monday, December 17, 2018 10:13 AM

All replies

  • It should be something like

    SELECT *
    FROM tableFood
    WHERE CategoryID Not In
        (SELECT CategoryID
         FROM tableCategories 
         WHERE Description<>"Meat Dish")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, December 15, 2018 7:14 PM
  • You can do that far more efficiently with a JOIN:

    SELECT tableFood.*
    FROM tableFood INNER JOIN tableCategories
    ON tablefood.CategoryID = tableCategories.CategoryID
    WHERE  tableCategories.Category <> "Meat Dish";

    However, for a recipes database I would normally have expected a many-to-many relationship type modelled by means of a table which resolves the relationship type into two one-to-many relationship types, e.g.

    Recipes
    ….RecipeID  (PK)
    ….RecipyName

    Ingredients
    ….IngredientID  (PK)
    ….Ingedient
    ….FoodCategoryID  (FK)

    FoodCategories
    ….FoodCategoryID  (PK)
    ….FoodCategory

    QuantityUnits
    ….QuantityUnitID  (PK)
    ….QuantityUnit

    and to model the relationship type between Recipes and Ingredients:

    RecipeIngredients
    ….RecipeID  (FK)
    ….IngredientID  FK)
    ….Quantity
    ….QuantityUnitID  (FK)

    The primary key of RecipeIngredients is a composite of RecipeID and IngredientID.  To return recipes with no meat would, with the above model, now require a subquery, e.g.

    SELECT DISTINCT RecipeName
    FROM Recipes INNER JOIN RecipeIngredients
    ON Recipes.RecipeID = RecipeIngredients.RecipeID
    WHERE NOT EXISTS
         (SELECT *
          FROM Ingredients INNER JOIN FoodCategories
          ON Ingredients.FoodCategoryID = FoodCategories.FoodCategoryID
          WHERE Ingredients.IngredientID = RecipeIngredients.IngredientID
          AND FoodCategory = "Meat");

    To return not only the recipe names, but also their ingredients and quantities, the RecipeIngredients, Ingredients, and QuantityUnits tables would also be included in the outer query, and the DISTINCT predicate omitted.

    Ken Sheridan, Stafford, England

    Sunday, December 16, 2018 12:21 PM
  • Ken,
    thanks this seems to be the kind of thing I need.

    I am sorry I was not as clear as I should be.    And also for using "Food" as a example, that was a bad choice. I made it seem if I was ONLY trying to exclude one "Category"  or item. 

    This was to be used for database of vendors,  with a table of items they might supply.

    A vendor might sell one or many of the items in tblItems. 

    So I used a many-to-many table called tblVendorItems using the primary key of the vendor in one column and the primary key of the item sold in each record in my tblVendorItems. 

    In tblVendorItems like all tables I used a AutoID field for the ID of each record to avoid problems when I used the table in a DAO Dynaset recordset. 

    I am trying to end up with a query "QryItemsVendorNotListedAsSelling" of ALL items in tblItems  that a vendor does NOT show as selling in tblVendorItems.    

    I am getting the value of VendorID from the form "frmVendorContactInfoAndSellsItmes" based on tblVendors that tbl is only vendor contact info.  That frmVendorContactInfoAndSellsItmes also has subForm showing what Vendor shows as carrying at this time, and I am trying to create a ListBox that shows ONLY what the vendor does NOT show as carrying at this time, as per tblVendorItems.

    I am trying to create a query as a source for the ListBox called   LlstAddItemsSoldToVendor that DOES NOT INCLUDE what items I already have Added to tblVendorItems for the vendor id I have already added. 

    I will be getting the vendorID from the frmVendorContactInfoAndSellsItmes.

    I apologize again for not being clear on what I am trying to do.

    I do want to thank you for your helpful example.

    Mark J


    Mark J

    Monday, December 17, 2018 9:15 AM
  • You'd still use the NOT EXISTS predicate, e.g.

    PARAMETERS Forms!rmVendorContactInfoAndSellsItmes!VendorID LONG;
    SELECT ItemID, Item
    FROM tblItems
    WHERE NOT EXISTS
         (SELECT *
           FROM tblVendorItems
           WHERE ItemID = tblItems.ItemsID
           AND VendorID = Forms!rmVendorContactInfoAndSellsItmes!VendorID
    ORDER BY Item;

    Requery the list box in the form's Current event procedure, and in the subform's AfterUpdate and AfterDelConfirm event procedures.

    Ken Sheridan, Stafford, England

    • Marked as answer by PuzzledByWord Monday, December 17, 2018 10:35 AM
    Monday, December 17, 2018 10:13 AM
  • Ken,

    Thank you very much.

    I also see why the other times I had tried to use "Parameters" in a qry why it might not have worked. I didn't use "Long" at the end for the last field.

    This is a solution for several databases I need to recreate. with the same requirement.

    Thanks,

    Mark J


    Mark J

    Monday, December 17, 2018 10:33 AM