georeference.org
Subscribe to this thread
Home - General / All posts - Easy question regarding query building from new user
rnowak21 post(s)
#11-Jun-12 17:39

I've been using these forums for a few months now as I have been learning Manifold. All great information and helpful. Thank you.

That said, I've run into an issue as I'm trying to build a query. Essentially, I have 2 tables. Each contains a column of zip codes. I'm looking to determine which zips from T1 aren't in T2, and which from T2 aren't in T1. Two different queries.

I haven't gotten very far. I tried doing something like (with C being the column containing the zip codes in the different tables)

SELECT* FROM T1, T2

WHERE Contains (T1.C1], (T2.C2)

That isn't getting me very far, as I'm sure the people that know what they're doing realize. Any ideas on how to solve this would be helpful! Thanks.

KlausDE

5,612 post(s)
#11-Jun-12 18:38

Hello @rnowak. Contains() is a spatial function only. Not what you want here.

--SQL

SELECT * FROM T1 

    WHERE NOT EXISTS 

    (SELECT * FROM T2 WHERE T1.C1 = T2.C2)

Is there an Excel or even an Access freak around who can do this in 3 lines of code?

mlinth
402 post(s)
#11-Jun-12 19:37

Here's the three lines of SQL. This should give you both.

select * from T1 FULL OUTER JOIN T2 on C1 = C2

WHERE C1 is null

OR C2 is NULL

Finding those zips which appear in neither table is harder

If you just want a "one way" query, you can try:

select T1.* from T1 LEFT JOIN T2 on C1 = C2

WHERE C2 is null

Hopefully you get the idea...

M

rnowak21 post(s)
#11-Jun-12 20:31

These two replies have gotten me well on my way.

Thanks again.

Manifold User Community Use Agreement Copyright (C) 2007-2011 Manifold Software Limited. All rights reserved.