O$: SQL Statement

[David Swain]

I had in mind something like:

SELECT * FROM
(SELECT * FROM CONTACTS, CONTACT_CAT  Where CC_CM_SEQ =* CM_SEQ_NO)  
AS temp
where CC_LM_SEQ is null or CC_LM_SEQ = 1

If this gets the set of data you want, you could then use GROUP BY in  
the outer query (the one outside the parentheses) to consolidate the  
list.

Regards,

David Swain

On May 30, 2008, at 1:20 PM, Christine Penner wrote:

> What do you mean by outer query. What might that look like. It's  
> been a while and my SQL is still a bit rusty.
>
> At 09:32 AM 30/05/2008, you wrote:
>> Christine,
>>
>> Since you can't perform a proper outer join (with LEFT JOIN...ON
>> syntax) using OmnisSQL, could you try performing the basic =* join
>> WITHOUT the (is null or =1) part of the where clause as a subquery
>> and then in the outer query add that further restriction? My guess is
>> that part of your where clause is knocking out the mismatches (by
>> contradicting the rest of the where clause).
>>
>> The "left join on join criteria" syntax preserves the mismatches you
>> want to retrieve in the virtual table created within the FROM clause.
>> The WHERE clause can then act by **targeting** those rows with NULL
>> entries in the columns derived from the dependent table.
>>
>> I don't use OmnisSQL in production and have no time to test now, but
>> this is a principle I teach in MySQL classes all the time.
>>
>> On May 30, 2008, at 11:43 AM, Christine Penner wrote:
>>
>>> Is Null may be supported but it doesn't give me what I want. I can
>>> remove the is null part and my results are the same. I get
>>> everything. I can't reverse the Join because what i want is records
>>> from the CM_SEQ_NO table that have no records in the CC_CM_SEQ
>>> table, not the other way around.
>>>
>>> At 06:01 PM 29/05/2008, Steve Finger wrote:
>>>> Is Null is definitely  supported by OmnisSql as is the join you
>>>> need. Did you try reversing:
>>>>
>>>> CC_CM_SEQ =* CM_SEQ_NO to
>>>> CM_SEQ_NO =* CC_CM_SEQ
>>>>
>>>> Outer joins are very slow in OmnisSql but they do work. It's been
>>>> a while and I don't have a test database to try it on but I'm
>>>> pretty sure you can get the results you want
>>>>
>>>> Christine Penner wrote:
>>>>> 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, David Swain wrote:
>>>>>> 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.
>>>>>>
>>>>>> 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?