Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering on Many-to-Many Relationships

    I have a many-to-many relationship (through a link table) between a main table and a "keyword" table; i.e., various keywords are assigned to each record of the main table. I'd like to filter the main table based on various keyword combinations. For example: "Show me all records that are assigned Keyword A and Keyword B, but not Keyword C" or "Show me all records that are assigned Keyword A or Keyword B", etc.

    What's the best way to set up a query to do this? My preference would be that the query is updatable (allowing changes to the main table). If there's not an easy way to do this (but I suspect there is and I'm just missing it), is there a better way to set up the tables that would easily allow this sort of thing?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering on Many-to-Many Relationships

    The problem is not so much creating a query to do this, but to design a user-friendly interface.

    Say that you have the following tables:
    - A main table tblMain with fields MainID (numeric, primary key) plus whatever you need.
    - A keywords table tblKeywords with fields KeyID (numeric, primary key) and Keyword (text)
    - A link table tblJoin with fields MainID and KeyID (composite primary key)

    An updateable query selecting records with keywords "A" and "B" but not "C" would look like this:

    SELECT tblMain.*
    FROM tblMain
    WHERE tblMain.MainID In (SELECT tblJoin.MainID FROM tblJoin INNER JOIN tblKeywords On tblJoin.KeyID = tblKeywords.KeyID WHERE Keyword In ("A", "B")) And tblMain.MainID Not In (SELECT tblJoin.MainID FROM tblJoin INNER JOIN tblKeywords On tblJoin.KeyID = tblKeywords.KeyID WHERE Keyword In ("C"))

    If you used combo boxes to let the user select a KeyID by pointing at a keyword, the query can be simpler, you don't need tblKeywords.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering on Many-to-Many Relationships

    Hans,

    Just what I needed! I've rarely used SELECT clauses imbedded in the WHERE clause so that hadn't occurred to me. Thanks for refreshing my memory -- I probably still would have had trouble getting the syntax just right without your nice example!

Posting Permissions

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