O$: SQL Statement

David Swain dataguru at polymath-bus-sys.com
Thu May 29 15:26:07 EDT 2008


Hi Christine,

What you want to use is an outer join. I can only speak for MySQL  
here, but try this:

SELECT * FROM CONTACTS left join CONTACT_CAT on CC_CM_SEQ = CM_SEQ_NO  
where CC_LM_SEQ is null or CC_LM_SEQ = 1

The left join (left because the "reference" table CONTACTS is on the  
left side of the word "join" as written here) preserves all rows in  
the reference table by default and brings along any matching rows  
from the other table. The join...on syntax operates WITHIN the from  
clause, building a virtual table on which the where clause will  
operate. The where clause then chooses those rows where the category  
is 1 or is null.

I do not know whether this is supported by OmnisSQL... I can  
guarantee it works with MySQL, though.

HTH!

Regards,

David Swain

On May 29, 2008, at 2:22 PM, Christine Penner wrote:

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



More information about the omnisdev-en mailing list