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

    Unanswered: SQL Exclude question

    i have a sql question... i have two tables one a model table. with modelid's and also a exclude table which also has modelid's. I want to create a query where i want to only show the modelid that do not match what is in the model table. How would i write that?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    SELECT m.*
       FROM model AS m
       LEFT OUTER JOIN exclude AS e
          ON (e.modelid = m.modelid)
       WHERE  e.modelid IS NULL
    -PatP

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    From what I understand, you want to show modelids from table "exclude" that don't match any modelid from table "model".

    Try this :

    select exclude.modelid from exclude where exclude.modelid not in (select model_id from model);

    Regards,

    RBARAER

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you are using MySQL 4.1 or newer, I'd recommend using the EXISTS test instead of IN because it is usually much more efficient.

    The outer join syntax that I originally posted works on all versions of MySQL.

    -PatP

Posting Permissions

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