Re: Help with simple query

On Fri, 30 Mar 2001 16:27:57 GMT, Rob Cecil <rcecil_at_requesthealth.com> wrote:

>This is a multi-part message in MIME format.
>--------------742A0F09052901037B5FFD85
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>Two tables - X, Y
>Y has a one-to-many relationship to X, i.e. has a FK back to X
>Y has a single field 'Foo', string type
>
>How would query be constructed that expresses:
>
>Select all rows in X where there are no rows in Y where Y.Foo is
>'Monday'
>

Wow, that is worded in a confusing manner. Perhaps you should clarify it a bit further next time to help dolts such as myself understand.

I'll take a guess:

SELECT * FROM X WHERE NOT EXISTS (SELECT * FROM Y WHERE Id = X.Id AND LOWER(Foo) = 'monday');

If you can guarantee that the formay is 'Monday' without any change in case, the query would be faster without the function lowering it.

hth,
Brian Received on Fri Mar 30 2001 - 10:49:08 CST