Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Posts
    1

    Unanswered: query (deals with exclusions)

    I have 2 tables. The first table is a master table with 3 fields: record id, list, and value. The second table is a lookup table that has the list and value in it, as well as fieldname. I need a query that will return a count of records in the master table that do not exist in the lookup table based on list and value. It seems straightforward but my brain doesn't seem to be working. I keep returning the count of records that don't match in the lookup table, instead of the master table. can anyone help? This was my code that isn't working:

    select m.list, m.value, count(*)
    from master m, lookup l
    where m.list = l.list and
    m.value <> l.value
    and fieldname = 'BOC'
    group by m.list, m.value

    Thanks in advance.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    If the columns LIST and VALUE are the joining columns between the MASTER table and the LOOKUP table then try this query:

    Code:
    SELECT COUNT(*)
    FROM MASTER m
    WHERE NOT EXISTS
        (
        SELECT *
        FROM  LOOKUP l
        WHERE l.LIST = m.LIST
        AND   l.VALUE = m.VALUE
        )
    MCDBA

Posting Permissions

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