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

Peter Haworth pete at lcsql.com
Wed Oct 9 00:12:08 EDT 2013


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
>



More information about the Use-livecode mailing list