Try this. I couldn't find if MYSQL supports not in's so I followed there
outer joins. You can read up on in in the mysql manual, do a search on
outer joins.
select base.zipcode,properties.zipcode from base right join properties on
base.zipcode=properties.zipcode where base.zipcode is null;
On Mon, Aug 20, 2001 at 04:39:11PM -0400, Chad Day wrote:
> I have 2 tables:
>
> bases, and properties
>
> base has a ZIPCODE field in the table, as does the properties table.
>
> I'm trying to find all bases that do NOT have a property in that zip code.
>
> What I've tried is:
>
> select distinct cb.*, cp.* from classified_bases as cb left join
> classified_properties as cp ON cb.ZIP != cp.ZIPCODE where cb.BASEORLOC = 'B'
> and cb.STATE = 'VA' AND cb.STATE = cp.STATE;
>
> but this returns 4 entries for every base (except the one where there is a
> record, where it returns 3), since there are 4 entries in the properties
> table. I know WHY it's doing it, it's comparing every base record to each
> single record in the property table .. I just don't know how to fix it to do
> what I want.
>
> I tried to explain this as best I could, if I need to clarify it some more,
> let me know.
>
> Thanks,
> Chad
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
