Manifold Community Site: Easy question regarding query building from new user

RE: Easy question regarding query building from new user

rnowak — Mon, 11 Jun 2012 17:39:20 GMT

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.

RE: Easy question regarding query building from new user

KlausDE — Mon, 11 Jun 2012 18:38:25 GMT

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?

RE: Easy question regarding query building from new user

mlinth — Mon, 11 Jun 2012 19:37:49 GMT

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...

RE: Easy question regarding query building from new user

rnowak — Mon, 11 Jun 2012 20:31:32 GMT

These two replies have gotten me well on my way.

Thanks again.