Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    santa clara
    Posts
    25

    Unanswered: Not sure how to make this Query

    I have a query which outputs some information but I need to limit this list via a table. I need to exclude certain rows that if they are in the exclude table not show the record.

    Here's what I have in my main table.

    Modelid - int
    Modelname - text
    partid - text
    Manufacturername - text
    OEMnumber - text

    In my exclude table I have

    partid - text
    modelid - id

    So i want to excude any models that have the partid and modelid if they are the same in the exclude table.

    For example..

    Modelid - 100
    Modelname - 'xp1'
    partid - 'part100'
    Manufacturername - 'mfr1'
    OEMnumber - 'oem1'

    if the exclude table had

    partid - 'part100'
    modelid - 100

    the modename would not show.

    Thanks for any help.

  2. #2
    Join Date
    Jun 2004
    Posts
    92
    Ok, here is a query that will filter out anything that matches either, partid or modelid or both:

    SELECT tblmain.modelid, tblmain.modelname, tblmain.partid, tblmain.manufacturername, tblmain.OEMnumber
    FROM tblmain LEFT JOIN tblexclude ON tblmain.partid = tblexclude.partid
    WHERE (((tblexclude.partid) Is Null) AND ((tblexclude.modelid) Is Null));

    It basically is an unmatched query.

  3. #3
    Join Date
    Oct 2003
    Location
    santa clara
    Posts
    25

    Almost

    This is almost what I want but it can't be partid or modelid or both. It just needs to be both. Is there a way I can tweak the query.

  4. #4
    Join Date
    Jun 2004
    Posts
    92
    Ok this should work for you:

    SELECT tblmain.modelid, tblmain.modelname, tblmain.partid, tblmain.manufacturername, tblmain.OEMnumber
    FROM tblmain LEFT JOIN tblexclude ON (tblmain.modelid = tblexclude.modelid) AND (tblmain.partid = tblexclude.partid)
    WHERE (((tblexclude.partid) Is Null) AND ((tblexclude.modelid) Is Null));

  5. #5
    Join Date
    Oct 2003
    Location
    santa clara
    Posts
    25

    Awesome

    Thanks. worked like a charm!

Posting Permissions

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