SQL (this AND that), OR (that AND theOther)

[Peter Haworth]

Whoops, that should be WHERE not SELECT...

Pete
lcSQL Software
On Oct 9, 2013 12:12 AM, "Peter Haworth" <pete at lcsql.com> wrote:

> Roger,
> Not 100% sure what you need but maybe the following might help.
>
> SELECT user IN ('Joe','John','Jim') AND car IN ('Ford','Chevy',whatever)
> GROUP BY car || user ORDER BY car
>
> Depending on the data, you might want to define a composite index on car
> and auto
>
> Pete
> lcSQL Software
> On Oct 8, 2013 7:17 PM, "Roger Eller" <roger.e.eller at sealedair.com> wrote:
>
>> According to this, UNION might get the results I am looking for, but will
>> be less efficient than a single SELECT.
>>
>>
>> http://stackoverflow.com/questions/542705/how-do-i-combine-2-select-statements-into-one
>>
>> ~Roger
>> On Oct 8, 2013 8:39 PM, "Roger Eller" <roger.e.eller at sealedair.com>
>> wrote:
>>
>> > John - Essentially, the queries are like this:  If there are 13 pair of
>> > 'AND' criteria, I would expect 13 rows of data to be returned.  The user
>> > can exist multiple times, but each car is unique.  In fact, car could
>> be a
>> > unique number like a VIN.
>> >
>> > My goal is to submit only 1 query, built from a list of (user AND car),
>> > and get back 1 row for each pair.  I'm not locked in to using OR if
>> there
>> > is a better way.
>> >
>> > SELECT * FROM myDatabase WHERE
>> >  ((user = 'Joe') AND (car = 'Ford')) OR
>> >  ((user = 'Joe') AND (car = 'Chevy')) OR
>> >  ((user = 'John') AND (car = 'Ford')) OR
>> >  ((user = 'John') AND (car = 'Chevy')) OR
>> >  ((user = 'John') AND (car = 'Kia')) OR
>> >  ((user = 'John') AND (car = 'Smart')) OR
>> >  ((user = 'John') AND (car = 'Volkswagon')) OR
>> >  ((user = 'John') AND (car = 'Honda')) OR
>> >  ((user = 'John') AND (car = 'Hyundai')) OR
>> >  ((user = 'John') AND (car = 'Dodge')) OR
>> >  ((user = 'John') AND (car = 'Saturn')) OR
>> >  ((user = 'Jim') AND (car = 'BMW')) OR
>> >  ((user = 'Jim') AND (car = 'Jaguar'))
>> > ORDER BY car DESC
>> >
>> > ~Roger
>> >
>> >
>> > On Tue, Oct 8, 2013 at 5:34 PM, John Craig <john at splash21.com> wrote:
>> >
>> >> For clarity, can you post the entire query?
>> >>
>> >>
>> >>
>> >> On 08/10/2013 21:52, Roger Eller wrote:
>> >>
>> >>>   Having some SQL query troubles...  I need to get results from a long
>> >>> list
>> >>> in a single query, BUT there are pairs of fields that must match.
>>  Every
>> >>> pair is a unique pair, aka no duplicates.  If my list contains 50 pair
>> >>> that
>> >>> are unique, I may only get 30 returned rows, but I need all 50.
>> >>>
>> >>> This is what I've tried, but SQL strips away my outer parens, making
>> my
>> >>> AND
>> >>> less binding for the pair.
>> >>>
>> >>> WHERE
>> >>>        ((user = 'Joe') AND (car = 'Ford')) OR
>> >>>        ((user = 'John') AND (car = 'Chevy')) OR
>> >>>        ((user = 'Jim') AND (car = 'Kia')) OR
>> >>>        ((user = 'Jim') AND (car = 'Smart'))
>> >>>
>> >>> ~Roger
>> >>> ______________________________**_________________
>> >>> use-livecode mailing list
>> >>> use-livecode at lists.runrev.com
>> >>> Please visit this url to subscribe, unsubscribe and manage your
>> >>> subscription preferences:
>> >>> http://lists.runrev.com/**mailman/listinfo/use-livecode<
>> http://lists.runrev.com/mailman/listinfo/use-livecode>
>> >>>
>> >>>
>> >>
>> >> ______________________________**_________________
>> >> use-livecode mailing list
>> >> use-livecode at lists.runrev.com
>> >> Please visit this url to subscribe, unsubscribe and manage your
>> >> subscription preferences:
>> >> http://lists.runrev.com/**mailman/listinfo/use-livecode<
>> http://lists.runrev.com/mailman/listinfo/use-livecode>
>> >>
>> >
>> >
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>