O$: SQL Statement
Christine Penner
christine at ingenioussoftware.com
Thu May 29 15:36:09 EDT 2008
Left Joins are not supported by Omnis. That's the reason for the =*.
Part of the problem is that I need this to work for Omnis SQL which
doesn't support many typical SQL features. The (is null) is not
supported by OmnisSQL and that's the real issue. I need another way
of saying is null.
Is there anyone who uses OmnisSQL who could help.
Christine
At 12:26 PM 29/05/2008, you wrote:
>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?
>_____________________________________________________________
>Manage your list subscriptions at http://lists.omnis-dev.com
More information about the omnisdev-en
mailing list