SQL Statement

[Chris Peck]

Use "=*" to give you joins that are not always there, but that might be
there.  I use that for all my 'vanilla' SQL statements.


Chris Peck
Software Developer
Word Master, Inc.
V: 847-948-9600
F: 847-948-9617


-----Original Message-----
From: omnisdev-en-bounces at lists.omnis-dev.com
[mailto:omnisdev-en-bounces at lists.omnis-dev.com] On Behalf Of Christine
Penner
Sent: Thursday, May 29, 2008 1:22 PM
To: omnisdev-en at lists.omnis-dev.com>
Subject: O$: SQL Statement

Hi All,

I have an SQL question. I'm currently using Omnis SQL but will be 
using Oracle, MySQL and possibly others. I have 2 tables I am 
joining. Contacts and ContactCategories. Contacts can have many 
categories but not all contacts have a category. I want to build an 
SQL statement that will give me all contacts that have no category OR 
have "category 1". I have tried a few things and none of it works. 
Here is what I've tried so far: (I realize the =* wont work for all 
back ends. This is temporary)

SELECT * FROM CONTACTS, CONTACT_CAT  Where CC_CM_SEQ =* CM_SEQ_NO AND 
((CC_LM_SEQ=1) OR (CC_LM_SEQ=0))
	; This gives me all contacts
	; If I get rid of the * after the = I get only the one with Category
1

SELECT * FROM CONTACTS, CONTACT_CAT  Where CC_CM_SEQ =* CM_SEQ_NO AND 
((CC_LM_SEQ=899) OR NOT(CC_LM_SEQ))
	; Gives an error

SELECT * FROM CONTACTS, CONTACT_CAT  Where CC_CM_SEQ =* CM_SEQ_NO AND 
((CC_LM_SEQ=899) OR ((CC_LM_SEQ) IS NULL))
	; Gives an error

I have also tried getting rid of the Category = 1 part (only show 
Contacts with no Category) and the results are the same. I either get 
everything or an error.

Anyone know how to do that?
Christine

_____________________________________________________________
Manage your list subscriptions at http://lists.omnis-dev.com