SQL Exclude question

pokermagic · #1 (**permalink**) 09-16-04, 13:58

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?

Pat Phelan · #2 (**permalink**) 09-16-04, 14:05

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

RBARAER · #3 (**permalink**) 09-16-04, 14:09

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

Pat Phelan · #4 (**permalink**) 09-16-04, 15:29

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

Internet Services

Web Development

Digital Marketing

Consumer Tech